Scripting

[shakeeb_mohammed]

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.

[Miguel_Angel_Baeyens]

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].

[shakeeb_mohammed]

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.

[juraj_misina]

That's exactly what it does. And you can use it in script as well.

[shakeeb_mohammed]

Thanks for the reply!

How would I put this in my script please?

[Peter_Cammaert]

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;