Two Separate Data Sources in One Sheet

[melissa4]

I need 2 different scripts in one app but they can't be connected to each other. They come from the same source but one gives cumulative data and the other does not. Is it possible to achieve this in Qlik Sense?

If I put both scripts in the Script Editor, Qlik automatically joins them using a synthetic key, even though I don't want it to. Alternatively I could use the CumulativeTable only and write the calculations in the sheet but I prefer to do it in the Script Editor as it can be more easily understood by the next developer and there are dozens of summary calculations I already created there.

Here are simplified versions of the 2 scripts I need:

CumulativeTable:
LOAD Reference_Nbr, Expense_Amount
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD]
(qvd)

SummarizeThisYearOnly:
LOAD Reference_Nbr, Sum(If(Project_Year=Num#(Date(Today(),’YYYY’)),Expense_Amount))
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD]
(qvd)
GROUP BY Reference_Nbr

[edwin]

you can concatenate them and add an identifier for you to refernce either detail data or aggregated data:

CumulativeTable:
LOAD Reference_Nbr, Expense_Amount, 'DETAIL' as FactType
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD]
(qvd)

concatenate(CumulativeTable)
LOAD Reference_Nbr, Sum(If(Project_Year=Num#(Date(Today(),’YYYY’)),Expense_Amount)) as Expense_Amount, 'AGGREGATED' as FactType
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD]
(qvd)
GROUP BY Reference_Nbr

in you expressions you add the set analysis sum({<FactType={'AGGREGATED'}>}Expense_Amount) or sum({<FactType={'DETAIL'}>}Expense_Amount)

[edwin]

or you can aggergate your in memory table:

CumulativeTable:
LOAD Project_Year, Reference_Nbr, Expense_Amount, 'DETAIL' as FactType
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD]
(qvd)

concatenate(CumulativeTable)
LOAD Reference_Nbr, Sum(If(Project_Year=Num#(Date(Today(),’YYYY’)),Expense_Amount)) as Expense_Amount, 'AGGREGATED' as FactType

resident CumulativeTable;

drop field Project_Year from CumulativeTable;  

[ArnadoSandoval]

Hi @melissa4 

Yes, you can do that within the Qlik Script, keeping in mind that qlik associate columns on different tables by their names, so, if you want the two tables separated, you just need to alias the column of any of the tables.

Your first query remains as it is, no changes:

CumulativeTable:
LOAD Reference_Nbr, Expense_Amount
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD](qvd)

 You implement the columns aliasing on the next LOAD, using preceding load, like this:

SummarizeThisYearOnly:
Load Reference_Nbr        As Year_Reference_Nbr,
     Sum(Expense_Amount)  As Year_Expense
Group By Reference_Nbr;

LOAD Reference_Nbr, 
     Expense_Amount
FROM [lib://FINANCE_QVDs/ACCOUNTING.QVD]
(qvd)
Where Project_Year=Num#(Date(Today(),’YYYY’));

HTH