topic Re: Nested loop problem in QlikView

Nested loop problem

Fri, 24 Aug 2012 16:04:19 GMT

Hello,

I am having a problem with a nested loops.

Problem description: A "Name" associated with a unique ID can change spellings over the different quarters(Time). So the Name associated with the latest quarter needs to be identified.

Problem requirement: Require a new field "RecentName" which holds the name of the most recent Quarter associated with the same ID

Attached an example application to explain the same.

Some rough code that i was trying, but not sure how to go about it:

let nbCID=FieldValueCount('ID');

for i=1 to nbCID

let refCID=FieldValue('ID', i);

let CName=Lookup('Name', 'ID', refCID, 'MyTable');

let nbquarter=FieldValueCount('[Quarter]');

for n=1 to nbquarter

...

next n

//concatenate(Table_principale)

//load distinct

//AutoNumberHash128('Quarter', [ID]) as _KEY_id,

//;

next i

Thanks a lot for pointers to the solution!

Regards,

Abhinav

Re: Nested loop problem

Nicole-Smith — Fri, 24 Aug 2012 16:38:40 GMT

I changed your load script to (also attached):

MyTable:

LOAD * INLINE [

Quarter, Name, Id

Q1, abcd, 2

Q1, hijk, 1

Q2, ab cd, 2

Q3, hijk, 1

Q4, Hijk, 1

Q4, mlk, 3

];

MyTable2:

NOCONCATENATE

LOAD Quarter,

Name,

RESIDENT MyTable

ORDER BY Id, Quarter;

DROP TABLE MyTable;

RecentName:

Load Id,

LastValue(Name) as RecentName

RESIDENT MyTable2 group by Id;

If you want RecentName to end up in the same table as the other stuff, change

RecentName:
Load Id, 
     LastValue(Name) as RecentName
RESIDENT MyTable2 group by Id;

INNER JOIN (MyTable2)
Load Id, 
     LastValue(Name) as RecentName
RESIDENT MyTable2 group by Id;

Re: Nested loop problem

Fri, 24 Aug 2012 22:17:49 GMT

Thank you a million Nicole Kowalsky!

That was a neat and clean solution! Unnnessearily i got in a wrong way of solving the problem...

I didnt know about LastValue. Thanks a lot!

Regards,