Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
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

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

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

Re: Adding indicator field in load script

Awesome

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

Community Browser