Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have 2 tables almost 6 fields are same, remaining fields are different..
Like..
Table1:
Load
SalesID |
ProdID, |
CountryCode, |
Sales |
Date |
ProdSesc |
SalesAmt |
From Sales;
Table2:
Load ProdID,
CountryCode,
Sales,
Date,
ProdDesc,
OrderAmt,
OredrName
From Orders;
i want to create link table, here should i create all common fields concatenate into one %Key field..
(Like ProdID & CountryCode & Sales & Date & ProdDesc as %Key)
or
any other method???
You can use As statement
Sales as T2Sales
or Qualify
Look here
Do you Qualify?- How to use QUALIFY statement
Feeling Qlikngry?
Take a look to autonumberhash256() instruction and to the following document:
Yes, that's the idea:
Table1:
LOAD
autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key,
...other fields...
FROM ...Table1Source...;
Table2:
LOAD
autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key,
...other fields...
FROM ...Table2Source...;
LinkTable:
LOAD distinct
autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key,
ProdId,
CountryCode,
Sales,
Date,
ProdDesc
FROM ...Table1Source...;
Concatenate(LinkTable)
LOAD distinct
autonumber(ProdID & '|' & CountryCode &'|' & Sales &'|' & Date &'|' & ProdDesc) as %Key
ProdId,
CountryCode,
Sales,
Date,
ProdDesc
FROM ...Table2Source...;
Note that the common fields now only exist in the LinkTable. Table1 and Table2 only contain %Key and the fields that are unique to the tables.
Hi Paul:
You can join both tables in only one
Table1:
Load
SalesID |
ProdID, |
CountryCode, |
Sales |
Date |
ProdSesc |
SalesAmt |
From Sales;
Left Join
Load ProdID,
CountryCode,
Sales,
Date,
ProdDesc,
OrderAmt,
OredrName
Hope this helps you
Joaquín
Hi Paul,
Find the attached app