left join table and converting rows to columns

[ronaldwang]

‌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

[PrashantSangle]

can you explain with small example??

Regards,

[settu_periasamy]

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';

[ronaldwang]

Hi Prashant,

The original table is like this:

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

[ronaldwang]

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

[jyothish8807]

Hi Ronald,

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

Br,

KC

[jyothish8807]

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;

[ronaldwang]

Hi KC, below is the second table

[jyothish8807]

Hi Ronald,

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

[ronaldwang]

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