Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
roman_7em
Contributor
Contributor

Create subquery column

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!

9 Replies
undergrinder
Specialist II
Specialist II

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.

roman_7em
Contributor
Contributor
Author

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
Specialist
Specialist

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
Specialist II
Specialist II

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
Specialist
Specialist

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
Specialist II
Specialist II

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
Specialist
Specialist

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
Specialist II
Specialist II

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.

roman_7em
Contributor
Contributor
Author

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