Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
shakeeb_mohammed
Contributor III
Contributor III

Scripting

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

SupplierLocationDelivery DateDate CreatedUserQty
AB01/12/201805/10/2018150
AA01/12/20104/10/2018225

What i want to show - 50

SupplierLocationDelivery DateDate CreatedUserQty
AB01/12/201805/10/2018150

Thanks.

5 Replies
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
Contributor III
Contributor III
Author

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
Luminary Alumni
Luminary Alumni

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

shakeeb_mohammed
Contributor III
Contributor III
Author

Thanks for the reply!

How would I put this in my script please?

Peter_Cammaert
Partner
Partner

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;