need implement condtion in table

[Raja_23]

I have a table widget it's have event number 1,2,3,4,5,6,7 with some shipment keys.

I created a max event number (new column in new table) with grouping of shipment key customer want to see only max event number row only. 

how to achieve this.

actual table:    

shipment key	event no	max event no
11423	1	5
11423	2	5
11423	3	5
11423	4	5
11423	5	5
11455	1	3
11455	2	3
11455	3	3

Expected output:

shipment key	event no	max event no
11423	5	5
11455	3	3

Not only shipment key I have some other categories also there. I need max event no= event no. 

raja. 

[HamzaJ]

Hey @Raja_23 ,

Is this on the datamodel or dashboard level? If its on the datamodel you could write a query similar to this:

select
a.*
from shipment_table a
join (select shipment_key, max(event_no) as max_event_no fro shipment_table group by shipment_key) b on b.shipment_key = a.shipment_key and b.max_event_no = a.event_no

If its on the dashboard-level, you could try to select Pivot , add the fields and add event_no as measure and select max. That should do the trick

Hamza

[Raja_23]

I have multiple table data involved in widget table

can we do right join relationship b/w tables?

[HamzaJ]

Hey @Raja_23 

That would be possible on a datamodel-level. On a dashboard-level doing something besides an inner join is difficult. If you can show me the schema and what you want I can take a look. If needed you can PM me with the info

Hamza

[Raja_23]

Can we connect in zoom call or any other share me the invite link. It cloud be very helpful for me.

[Raja_23]

@HamzaJ 

PFA.

TABLE: LATEST STATUS DETAILED VIEW

Thanks 

Raja.

[HamzaJ · Accepted solution]

Hey @Raja_23 

You could do this:

select
a.*
from vwShipmentStatus a
join (
select [ShipmentKey],max([ShipmentEventNumber]) as MaxShipmentEvent_NR
from vwShipmentStatus 
GROUP BY [ShipmentKey] ) b on a.[ShipmentKey] = b.[ShipmentKey] and a.[ShipmentEventNumber] = b.MaxShipmentEvent_NR

which will give you the result you are looking for (e.g. expected output).

If you want more flexibility you could also do 

select
a.*, 
b.MaxShipmentEvent_NR,
case 
when a.[ShipmentEventNumber] = b.MaxShipmentEvent_NR then 'Yes'
else 'No' 
end as Max_event
from vwShipmentStatus a
join (
select [ShipmentKey],max([ShipmentEventNumber]) as MaxShipmentEvent_NR
from vwShipmentStatus 
GROUP BY [ShipmentKey] ) b on a.[ShipmentKey] = b.[ShipmentKey] 

This will give you the total table with a flag (Max_event). You can filter on this to only show max_event or to show everything

Hamza

[Raja_23]

@HamzaJ 

When I'm using 2nd query's I got duplicates in table widget.

[HamzaJ]

Hey @Raja_23 

That is correct. The second query will give you the first table with an additional parameter which you can filter on (Yes/No) to show everything or to only show the Max-records. It gives you a bit more flexibility. However if you do not need it, you can just use query #1

Hamza

[Raja_23]

Hi @HamzaJ 

I tried 1st query also still duplicates coming can you check your end once you have file.

 Widget Table: LATEST STATUS DETAILED VIEW