Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can you explain with small example??
Regards,
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';
Hi Prashant,
The original table is like this:
After left joining itself, the result I want to achieve is like below:
thanks, will give it a try to see how it goes
Hi Ronald,
can you share your second table also / Which has only 3 dates ?
Br,
KC
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;
Hi KC, below is the second table
Hi Ronald,
Give a try to the below solution i mentioned. Use generic load.
thanks, I got it to work by using left join and where clause.