Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Can someone assist me with the below please?
I am getting my data from an excel spreadsheet which pushes the correct data range in Microsoft SQL database and loading it in to QlikSense
My problem is, when a user updates a field within the spreadsheet it adds a new line of field in the database and then duplicates that data with a unique ID in Qlik (Technically its not unique as it has a unique ID).
What i was hoping is can I show data for the MAX date and dis include any values before that date?
Current Data - Total 75
Supplier | Location | Delivery Date | Date Created | User | Qty |
---|---|---|---|---|---|
A | B | 01/12/2018 | 05/10/2018 | 1 | 50 |
A | A | 01/12/201 | 04/10/2018 | 2 | 25 |
What i want to show - 50
Supplier | Location | Delivery Date | Date Created | User | Qty |
---|---|---|---|---|---|
A | B | 01/12/2018 | 05/10/2018 | 1 | 50 |
Thanks.
If the case is as simple as that example, probably this would work:
FirstSortedValue(Qty, -[Date Created])
This function takes the value of Qty for the highest value of [Date Created].
Hi, thanks for the reply.
Its not necessarily the highest value - Note that the date created field is the latest data entry the Qty could go up or down.
I was hoping to put this in my script rather than in expressions on the front end so i will always have the latest data showing.
That's exactly what it does. And you can use it in script as well.
Thanks for the reply!
How would I put this in my script please?
There may be faster ways to do this (for example by first using the GROUP BY & FirstSortedValue combo on the unique row ID and then joining the rest) and the logic may not be applicable if Supplier isn't your only grouping field, but this would work for you example I guess:
LatestInfo:
LOAD Supplier,
FirstSortedValue(Location, -[Date Created]) AS Location,
FirstSortedValue([Delivery Date], -[Date Created]) AS [Delivery Date],
Max([Date Created]) AS [Date Created],
FirstSortedValue(User, -[Date Created]) AS User,
FirstSortedValue(Qty, -[Date Created]) AS Qty
FROM YourExcelFile (options)
GROUP BY Supplier;