cancel
Showing results for 
Search instead for 
Did you mean: 

need implement condtion in table

Raja_23
7 - Data Storage
7 - Data Storage

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. 

1 ACCEPTED SOLUTION

HamzaJ
12 - Data Integration
12 - Data Integration

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

View solution in original post

9 REPLIES 9

HamzaJ
12 - Data Integration
12 - Data Integration

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
7 - Data Storage
7 - Data Storage

I have multiple table data involved in widget table

can we do right join relationship b/w tables?

 

 

HamzaJ
12 - Data Integration
12 - Data Integration

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
7 - Data Storage
7 - Data Storage

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

Raja_23
7 - Data Storage
7 - Data Storage

@HamzaJ 

PFA.

TABLE: LATEST STATUS DETAILED VIEW

 

Thanks 

Raja.

HamzaJ
12 - Data Integration
12 - Data Integration

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
7 - Data Storage
7 - Data Storage

@HamzaJ 

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

HamzaJ
12 - Data Integration
12 - Data Integration

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
7 - Data Storage
7 - Data Storage

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