Forum Discussion

Raja_23's avatar
Raja_23
Data Storage
05-02-2023
Solved

need implement condtion in table

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. 

Dashboards & Reporting
  • HamzaJ's avatar
    HamzaJ
    05-03-2023

    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

9 Replies

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    05-02-2023

    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's avatar
    Raja_23
    Data Storage
    05-03-2023

    I have multiple table data involved in widget table

    can we do right join relationship b/w tables?

     

     

  • HamzaJ's avatar
    HamzaJ
    Data Integration
    05-03-2023

    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's avatar
      Raja_23
      Data Storage
      05-03-2023

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

    • HamzaJ's avatar
      HamzaJ
      Data Integration
      05-05-2023

      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's avatar
        Raja_23
        Data Storage
        05-05-2023

        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