Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

magertgen
New Contributor III

Adding indicator field in load script

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

UserMeasureDate
JohnAJuly 2015
JohnAAugust 2015
JohnASeptember 2015
JohnBSeptember 2015
JohnBOctober 2015
JohnBNovember 2015
SaraBAugust 2015
SaraBSeptember 2015
SaraCMarch 2015
SaraCApril 2015
SaraD

January 2015

1 Solution

Accepted Solutions
MVP
MVP

Re: Adding indicator field in load script

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:

Capture.PNG

3 Replies
MVP
MVP

Re: Adding indicator field in load script

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:

Capture.PNG

magertgen
New Contributor III

Re: Adding indicator field in load script

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

Highlighted
MVP
MVP

Re: Adding indicator field in load script

Awesome

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