Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | |
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.
Use the hierarchy function. See this blog post for more information on that function: Unbalanced, n-level hierarchies
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?
Use the hierarchy function. See this blog post for more information on that function: Unbalanced, n-level hierarchies
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?
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.