Do While Loop to find original report

[sifatnabil]

Hi all,

I have this business requirement:

1. When a report is submitted, it generates a unique Report ID in the database.

2. When the same report is re-submitted, a new Report ID is generated, but this new report has a link to the older report ID.

3. If a report is re-submitted multiple times, each Report ID will have a link to its older report.

4. In the scenario of multiple submitted reports, how can we get the original report ID?

The database looks like this:

Report ID	Older Report ID
5158	5069
3414	1544
5069	5064
3256	3252
4144	2445
5064	5049
5049	5046

In the above table (in red), report 5158 has an older report ID = 5069.

Report 5069 has an older report ID = 5064.

Report 5064 has an older report ID = 5049.

Report 5049 has an older report ID = 5046.

Hence, the final report 5158 has an original report = 5046. I need to create a mapping table where, when it's Report ID=5158, the second field returns 5046 instead of 5069. Can this be done with a do-while loop? I tried the below but it's not working -

let i=0;

let Id=5158;

	do while isnull(Id)=0 //only loop if older report ID exists.
	mappreviousform:
	load
	RecNo() as Row,
	Code, //Report ID
	Id    //Older Report ID

	resident cachebyid
	where Type=6
	and Code='$(Id)' //Assign older report ID to current Report ID field
	Order by Code desc;

	let Id=peek('Id',recno()+$(i),'cachebyid'); //next older report ID
	let i=$(i)+1;
	loop

[Gysbert_Wassenaar]

Use the hierarchy function. See this blog post for more information on that function: Unbalanced, n-level hierarchies

[sifatnabil]

Thanks Gysbert. Actually, I tried doing this in Microsoft SQL and it works, however I can't make it work on Qlikview because of the semicolon endings in the script I wrote. Do you know a way to keep semicolons in the SQL without Qlikview terminating the script?

[Gysbert_Wassenaar]

Use the hierarchy function. See this blog post for more information on that function: Unbalanced, n-level hierarchies

[sifatnabil]

Sorry gwassenaar looks like you repeated your original answer. I am trying to prevent Qlikview load script from ending an SQL statement which has a semicolon in between - is there a way to do this?

[Gysbert_Wassenaar · Accepted solution]

Yes, I repeated my answer because you should consider using the hierarchy function instead of trying to code a loop.

No. If you want multiple sql statements to be executed as a single statement then you will have to create a stored procedure or a function for that in your source database.