topic Re: Loop through table to find first value in QlikView

Loop through table to find first value

ciaran_mcgowan — Tue, 01 Aug 2017 09:27:07 GMT

Hi all,

I have an issue where I have to find the first ID of a row in a table for each row. Every row contains an ID, but it also has the ID of that row's predecessor and there is no limit to how many predecessors a row can have. See example below:

ID	Name	Previous ID	Complete
1	Person 1		No
2	Person 2	1	No
3	Person 3		No
4	Person 4	3	Yes
5	Person 5	2	No
6	Person 6	5	Yes

I need to add a column called Original ID which will look like below. Any thoughts?

ID	Name	Previous ID	Complete	Original ID
1	Person 1		No	1
2	Person 2	1	No	1
3	Person 3		No	3
4	Person 4	3	Yes	3
5	Person 5	2	No	1
6	Person 6	5	Yes	1

Re: Loop through table to find first value

Anil_Babu_Samineni — Tue, 01 Aug 2017 09:47:46 GMT

What was the logic behind? that may helps you

Re: Loop through table to find first value

Chanty4u — Tue, 01 Aug 2017 09:55:29 GMT

how the original id column defined from? what is the caluclation ?

Re: Loop through table to find first value

ciaran_mcgowan — Tue, 01 Aug 2017 10:03:44 GMT

I don't have access to the logic but it looks to be incremental rows. No set order or logic that I can use to link. I'm thinking that this is going to require multiple FOR loops.

Re: Loop through table to find first value

rahulpawarb — Tue, 01 Aug 2017 10:35:59 GMT

This can be achieved using Hierarchy function. Please refer attached sample application.

Regards!

Rahul Pawar

Re: Loop through table to find first value

ciaran_mcgowan — Tue, 01 Aug 2017 10:47:11 GMT

I created a hierarchy and joined it to my table using the ID fields and brought in the new field which was the top position from the hierarchy.

Thanks!

Re: Loop through table to find first value

rahulpawarb — Tue, 01 Aug 2017 10:58:39 GMT

Cheers!

Rahul Pawar