Adding indicator field in load script

[magertgen]

Hi all, I need a hand adding an indicator (1/0) field to a resident table.  Normally I'd do this in SQL as the data was coming in, but my data source is a stored proc, so I need to do this in my QV load script.

I have a table with User, Measure and Date.  I want to add an indicator field for the latest of each measure by each user.

Given the table below, I want to add a Current Record Indicator column that has a 1 for:

John / A / September 2015

John / B / November 2015

Sara / B / September 2015

Sara / C / April 2015

Sara / D / January 2015

For all other rows the indicator would be 0.  Using a group by I created a table with User, Measure and Max(Date).  Now I just have to figure out how to join/map the two tables so that I end up the indicator column added to the base table.

I'm sure this isn't complicated, but I can't figure out how to do it 'the QlikView way' and I'm just spinning my wheels now.

Any help would be greatly appreciated!

Thanks,

~Mark

User	Measure	Date
John	A	July 2015
John	A	August 2015
John	A	September 2015
John	B	September 2015
John	B	October 2015
John	B	November 2015
Sara	B	August 2015
Sara	B	September 2015
Sara	C	March 2015
Sara	C	April 2015
Sara	D	January 2015

[sunny_talwar · Accepted solution]

Try this:

SET DateFormat='MMMM YYYY';

Table:

LOAD User,

    Measure,

    Date#(Date, 'MMMM YYYY') as Date

FROM

[https://community.qlik.com/thread/189267]

(html, codepage is 1252, embedded labels, table is @1);

Join (Table)

LOAD User,

  Measure,

  Date(Max(Date)) as Date,

  1 as Flag

Resident Table

Group By User, Measure;

Output:

[magertgen]

YES!!  Sunny, thank you very much, this worked!  I had to jump through a few qualify/unqualify hoops, but this solved my ultimate problem!!

Thanks again!

~Mark

[sunny_talwar]

Awesome

I am glad you were eventually able to figure it out.