Create subquery column

[Anonymous]

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!

[undergrinder]

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.

[Anonymous]

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

[Quy_Nguyen]

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.

[undergrinder]

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.

[Quy_Nguyen]

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)

[undergrinder]

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:

• Create a cartesian product from the two table
• find the maximum date that not greater then the date column in the original table
• filtering out the unnecessary rows.

G.

[Quy_Nguyen]

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.

[undergrinder]

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.

[Anonymous]

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