Script flag - for unique order max date

[Lobo77]

Good afternoon all.

I have been trawling the forum for  last 2 days, however have not found a solution.

I would like to create a flag in the script that flags the “Order number” ( with a Yes , if not No )  for each unique “Refence ID”  with the Max “Date Updated”

A unique  “Reference ID” can have multiple  unique “Order numbers”.

I have found away to have a calculated dimension in the front end, however would like to try to have it in the script.

I must be missing something very simple as even the front end calculated dimension seems over the top.

Note: Unfortunately I cannot share app example due to privacy

Here is an example of what I am trying to achieve (flag) , but am after a script flag.

I am very new to the script side of things, please be gentle.

The script that is currently in use (see below)  is simply  two identical QVDs being concatenated ( one recent , one archive )

The “Date update” is being changed to Time stamp in the ‘Data Manager’. That is all. I am not sure to where even start.

LOAD
"Order number",
"Reference ID",
"Date updated"
FROM [********ONE.QVD]
(qvd)
;
LOAD
"Order number",
"Reference ID",
"Date updated"
FROM [********TWO.QVD]
(qvd)
;

Thank you for any assistance you can provide or point me to.

[marcus_sommer · Accepted solution]

The simplest method is probably to load the table resident with an appropriated sorting and then using an interrecord-function to check the previous record and to use it to set the flag, maybe something like this:

t: load *, if([Reference ID] <> previous([Reference ID], 1, 0)
resident YourTable order by [Reference ID], [Date updated] desc;

Important is the right sorting and that all relevant variants are queried within the if-loop. To avoid nested if-loops or to simplify the sorting it could be helpful to combine some of the fields to appropriate keys.

- Marcus

[jalpashukla · Accepted solution]

Try this method:

Left Join
Load "Reference ID",
date(max(Date Updated)) as "MaxDate"
Resident [tablename]
group by "Reference ID";

then you can create a flag with the help of maxdate

[Lobo77 · Accepted solution]

Thanks Both!

I ended up using something very similar to @jalpashukla  I ended up finding here : https://community.qlik.com/t5/QlikView-App-Dev/Calculated-Field-max-date-Y-N/td-p/61868

Thanks also @marcus_sommer  for the detailed explanation , I will also try this method and test results.
Thanks again to both!!

[Lobo77]

Hi @marcus_sommer  , I tested , and woks great. However , as I am new to scripting where best to place it ? 

I currently have in on a placed it on a left join after the 2 tables concatenated ( see below ) .

Is this how you would normally do  it ? Or would you add it to both Qvds prior ?

Left Join (Table)
Load
if([Reference ID] <> previous([Reference ID]),1, 0) as MaxDate,
[Date updated]
Resident NBN
Order by [Reference ID], [Date updated] desc;

FinalData:

NoConcatenate
LOAD *
Resident Table;

DROP Table Table;

[marcus_sommer]

In regard to your origin example I would apply it against the entire (concatenated) table. But by using any incremental approaches I would consider to add these information already within any of the qvd-layers.

- Marcus