Passing the values from one table to another table in Qlik Scripting

padmashini_rama — Wed, 12 Jun 2019 10:45:27 GMT

Hi,

Warm greetings!..

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)

Query:1

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 (

Query2:.

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.

Regards,

Padmashini

topic Passing the values from one table to another table in Qlik Scripting in App Development

Passing the values from one table to another table in Qlik Scripting