Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
A7R3
Contributor III
Contributor III

Straight table - Data of last date and 2nd last date in one row

Hi Experts,

I have a requirement to organize data by its dates in a single row in a straight table. 

This is how the raw data looks like.

MaterialGroupColorNameLatestControllerUpdate01DateControllerUpdate
612121. Dark RedLJ5pcs ETA 30Apr4/15/2021
612121. Dark RedLJ10pcs ETA 30Apr4/18/2021
121851. Dark RedLJdemand upside 4/15/2021
129541. Dark RedLJchange to A/F4/15/2021
129541. Dark RedLJGR on Apr 184/18/2021
170571. Dark RedLJexpediting 4/15/2021
170571. Dark RedLJStill expediting4/18/2021

 

This is the desired outcome, where the Latest Update should be the data of the latest date.  So for 12185, it should be Apr 15, and for others, they should be Apr 18.

MaterialGroupColorNameLatestLatest UpdatePrevious Update
612121. Dark RedLJApr18: 10pcs ETA 30AprApr15: 5pcs ETA 30Apr
121851. Dark RedLJApr15: demand upside -
129541. Dark RedLJApr18: GR on Apr 18Apr15: change to A/F
170571. Dark RedLJApr18: Still expeditingApr15: expediting 

 

I can get it partially done, with Max(Date) and Max(Date,2), but the output looks like this. 

MaterialGroupColorNameLatestIF(DateControllerUpdate='$(=Max(DateControllerUpdate))', Date(DateControllerUpdate,'MMMDD')&': '&ControllerUpdate01)IF(DateControllerUpdate='$(=Max(DateControllerUpdate,2))', Date(DateControllerUpdate,'MMMDD')&': '&ControllerUpdate01)
612121. Dark RedLJApr18: 10pcs ETA 30Apr-
612121. Dark RedLJ-Apr15: 5pcs ETA 30Apr
121851. Dark RedLJ-Apr15: demand upside 
129541. Dark RedLJApr18: GR on Apr 18-
129541. Dark RedLJ-Apr15: change to A/F
170571. Dark RedLJApr18: Still expediting-
170571. Dark RedLJ-Apr15: expediting 

 

How do I get the update of the same material number on the same row?

Appreciate any advice that you can give.

Thank you in advance.

Labels (4)
1 Solution

Accepted Solutions
Digvijay_Singh

Something like this may be - 

 

Digvijay_Singh_1-1620877101134.png

 

View solution in original post

4 Replies
Digvijay_Singh

try with firstsortedvalue function, that should help in this situation. You can find many good posts here in community.

Digvijay_Singh

Something like this may be - 

 

Digvijay_Singh_1-1620877101134.png

 

mwanders
Partner - Creator
Partner - Creator

Hi,

If I have to do this then I will create two columns in the load script. I don't know if you have the possibilty to do this. Or maybe your data set is large.

One date you can create with the MAX function. The other date from a sorted table (max date on first row) and then use the PEEK function to the second row to get the second date. Both columns you can map to your existing table.  Your calculation will be also faster in your dashboard.

And even you can put the two dates in order in one column with the CONCAT funtion in your script.

 

A7R3
Contributor III
Contributor III
Author

Thank you for the effort to work out the expression. It helps to confirm the solution quickly. Appreciate it.