Nested loop problem

[#1]

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

[Nicole-Smith · Accepted solution]

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,
     Id
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;

to

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

[#3]

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,