topic left join table and converting rows to columns in QlikView

left join table and converting rows to columns

ronaldwang — Wed, 25 Nov 2020 16:16:04 GMT

‌I have an original table where the dimensions are company code, date and stock price. I want to link to another table with the same table structure but only three different values in date dimension. I am to use left join function. And the end result I want to achieve is having three additional columns(representing 3 different values in the second table) and the value in these three dimensions will be the corresponding stock price on those days. Any idea how I can achieve this? Thanks

Re: left join table and converting rows to columns

PrashantSangle — Tue, 21 Nov 2017 06:07:39 GMT

can you explain with small example??

Regards,

Re: left join table and converting rows to columns

settu_periasamy — Tue, 21 Nov 2017 06:49:17 GMT

Hi,

May be try this..

If it is only 3 dimensions needs to add, you can filter one by one and do the left join.

Table1:

Load CompanyCode,

Date,

Stock_Price

From Table1;

Left Join (Table1)

Load CompanyCode,

'Field1' as Field1

from Table2 where Date='Filedvalue1';

Left Join (Table1)

Load CompanyCode,

'Field2 as Field2

from Table2 where Date='Filedvalue2';

Re: left join table and converting rows to columns

ronaldwang — Tue, 21 Nov 2017 07:11:22 GMT

Hi Prashant,

The original table is like this:

After left joining itself, the result I want to achieve is like below:

Re: left join table and converting rows to columns

ronaldwang — Tue, 21 Nov 2017 07:11:48 GMT

thanks, will give it a try to see how it goes

Re: left join table and converting rows to columns

jyothish8807 — Tue, 21 Nov 2017 08:10:41 GMT

Hi Ronald,

can you share your second table also / Which has only 3 dates ?

Br,

Re: left join table and converting rows to columns

jyothish8807 — Tue, 21 Nov 2017 08:29:16 GMT

Try like this: Generic load is the solution in this case.

Table11:

LOAD CID,

Date,

Sale

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

Table1:

LOAD CID,

Date,

Sale

FROM

(ooxml, embedded labels, table is Sheet2);

temp1:

Generic LOAD * resident Table1;

Left join (Table11)

result:

LOAD Distinct CID Resident Table1;

DROP Table Table1;

Re: left join table and converting rows to columns

ronaldwang — Wed, 22 Nov 2017 00:50:39 GMT

Hi KC, below is the second table

Re: left join table and converting rows to columns

jyothish8807 — Wed, 22 Nov 2017 05:07:13 GMT

Hi Ronald,

Give a try to the below solution i mentioned. Use generic load.

Re: left join table and converting rows to columns

ronaldwang — Wed, 22 Nov 2017 06:09:28 GMT

thanks, I got it to work by using left join and where clause.