topic Re: Scripting in QlikView

Scripting

shakeeb_mohammed — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Scripting

Miguel_Angel_Baeyens — Fri, 05 Oct 2018 14:06:20 GMT

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

Re: Scripting

shakeeb_mohammed — Fri, 05 Oct 2018 14:11:17 GMT

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.

Re: Scripting

juraj_misina — Fri, 05 Oct 2018 14:51:44 GMT

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

Re: Scripting

shakeeb_mohammed — Mon, 08 Oct 2018 08:39:19 GMT

Thanks for the reply!

How would I put this in my script please?

Re: Scripting

Peter_Cammaert — Mon, 08 Oct 2018 10:12:37 GMT

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;