topic Re: Adding indicator field in load script in QlikView

Adding indicator field in load script

magertgen — Tue, 27 Oct 2015 15:08:22 GMT

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

Re: Adding indicator field in load script

sunny_talwar — Tue, 27 Oct 2015 16:06:25 GMT

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:

Re: Adding indicator field in load script

magertgen — Tue, 27 Oct 2015 18:30:25 GMT

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

sunny_talwar — Tue, 27 Oct 2015 18:33:51 GMT

Awesome

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