Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody! I am new to Qlik Sense and have a question. I am loading two sets with data without any relations between their fields. I want to create a subquery column based on data from the two downoaded sets. How I can resolve this issue? Should I somehow make this in Data Loader script or already in the script on the "table" element? Thank you!
Hi Roman,
the example data would help.
If the data sets have nothing in common, just want to add to the application, then load as two separate table.
Table 1:
Load
Fields...
From/Resident Table;
Table 2:
Load
Fields...
From/Resident Table;
What would it be it's purpose?
G.
Hi Gabor! Thanks for your answer. Here is some additional information.
I need only one table. This table should display columns from Table_1 and one column, where I should calculate data based on Table_2
here is simple equivalent code on SQL:
SELECT
Col1,
Col2,
Date1,
.
.
.
(SELECT TOP(1)
Table_2 .Price
FROM Table_2
WHERE Table_2.Date2 < Table_1.Date1
ORDER BY Table_2.Date2 DESC)
FROM Table_1
Any help? I think it's a challenge.
As me, i will need to create a loop to achieve this in Qlik script.
Waiting for solution from expert.
I see.
The provided SQL does represents your needs?
So you want join an extra column to the base dataset, but retain only the most recent value based on date?
G.
Sorry Gabor, I was supposed to reply the original post.
For his case, i think that he want to join an extra column (from other table) to the dataset, based on the date (get the value of most recent date in other table)
Hi Quy,
I think the solution could be like this:
base:
Load * inline
[
value|date
a|2018-08-01
b|2018-09-05
c|2018-09-10
] (delimiter is '|');
prices:
Load * Inline
[date|price
2018-06-01|10
2018-07-05|11
2018-08-05|10
2018-08-21|12
2018-09-04|13
] (delimiter is '|');
//create a cartesian product
tmp:
Load
value,
date as date_fact,
1 as key
Resident base;
Left join(tmp)
Load
date as date_price,
price,
1 as key
Resident prices;
drop table base;
drop table prices;
//find maximum date group by
Left join(tmp)
Load
value,
date_fact,
max(if(date_fact>=date_price,date_price,null())) as max_dt
Resident tmp
Group by value,
date_fact;
//filtering out the extra rows
final:
Load
value,
date_fact,
price,
date_price
Resident tmp
Where date_price=max_dt;
drop table tmp;
I know this should be go below the question, but we start a conversation anyway
So:
G.
Hi Gabor,
I agree with your solution. But is there any way to achieve it? As i see in the script you did a join 2 tables with no relative column ( no need to create key in this case, Qlik will do it automatically) -> result will be a m x n matrix, need high performance.
Yes it has a bad performance, but we don't have non-equi join option in Sense, so I think there is no better option in this case.
G.
Hi Gabor! Your solution is not exactly what I need, but thank you for your help in solving this issue.
I have one more question. Is it possible to do this without joins between these data sets and further group them?
Does qlick support syntax like below:
base:
Load * inline
[
value|date1
a|2018-08-01
b|2018-09-05
c|2018-09-10
] (delimiter is '|');
prices:
Load * Inline
[date2|price
2018-06-01|10
2018-07-05|11
2018-08-05|10
2018-08-21|12
2018-09-04|13
] (delimiter is '|');
final:
Load
value,
date1,
(LOAD FirstSortedValue(price, date2);
SELECT price, date2
FROM prices where date2 <= date1) as FinalPrice
Resident base