Passing the values from one table to another table in Qlik Scripting
Need help on converting the given sub-queries to Qlik where we are passing the values on EFF_DT and Revision_Num fields from one table to another table.
We need to get the Office and team details using below queries.
Below are the two challenges we are facing to implement in Qlik:
1. Pass the value of "Last Work Date" from "Report" table to "EFF_DT" in "Revision" table as given in below Query1.
2. Pass the Revision_Num value from Query1 to next Query2 as Revision_Num <= (Query1 Value)
SELECT A.Revision_Num FROM Revision A WHERE A.Report_Num = 315905 AND A.Coverage_Code = '8200' AND A.Plan_Code = '000' AND A.Revision_Num = (SELECT MAX(B.Revision_Num) FROM Revision B WHERE B.Report_Num = 315905 AND B.Coverage_Code = '8200' AND B.Plan_Code = '000' AND B.Dept_Type ¬= 'C' AND B.EFF_DT <= DATE('1/1/2019') -- (Last work date from Report Table) AND NOT EXISTS ( SELECT 1 FROM Revision C WHERE C.RPL_TS = B.UPDT_TS ))
As output, will get Revision_Num from above Query1 and then we need to pass the same value in below Query2 to another Team table. --Query1_Value (
SELECT Team,A.Office FROM Team A WHERE A.Report_Num = 315905 AND A.Coverage_Code = '8200' AND A.Plan_Code = '000' AND A.Sub_Code = '0001' AND A.SubPoint_Code = '0001' AND Revision_Num <= Query1_Value AND NOT EXISTS ( SELECT 1 FROM Team B WHERE B.Revision_Num <= Query1_Value--Revision_Num from Query1 AND A.UPDT_TS = B.RPL_TS )
Attaching the sample data for Report and Revision table.
Please let me know if any other details are required to solve this.
Our Solution: we don’t find an easy way in Qlik on converting the query logic other than iteratively executing the query. But this iteration method takes hours to finish, so we cannot go with this approach either.
So requesting your help on converting the same sub query logic to pass the values in Qlik tables considering the run time and performance.