Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
claudialet
Contributor III
Contributor III

How to separate 2 concatenated resident tables ?

  There are 2 tables - Sales and Plan. These two tables were created from  resident tables.  

 This is complicated.  Right now the 2  table are concatenated.   I need to separate them and create link tables. 

How do I create a link table between concatenated tables ?

The only way the big Sales table shows in the data model is with the CONCATENATE script.  If I alter that there is no fact table in the model

Plan:
load
Company ,
[Brand Code] ,
Market ,
[Order Type Code] ,
[Sales Plan Amount] ,
[Cost Plan Amount] ,
[Sales Plan Quantity] ,
ApplyMap('PeriodMap', text([Year]) & '|' & text([Period])) as [ key_Date]
resident F100;

Sales:
load
[Company] ,
[Brand Code] ,
[Market] ,
[Order Type Code] ,
[key_Date] 


resident F200;


CONCATENATE (Sales) load
Company,
[Brand Code],
Market,
[Order Type Code],
[Sales Plan Amount],
[Cost Plan Amount],
[Sales Plan Quantity],
ApplyMap('PeriodMap', text([Year]) & '|' & text([Period])) as key_Date


resident Plan;

drop table Plan;

3 Replies
anushree1
Specialist II
Specialist II

I am not very sure of your requirement, could you please explain in detail?

claudialet
Contributor III
Contributor III
Author

 Plan and Sale  are concatenated . 

They share  common fields 

Company
Brand Code
Market
Year
Period

How do I separate Plan and Sales by creating link table ? 

asinha1991
Creator III
Creator III

I don't know from where you are doing resident , to avoid auto concatenate I will use aliases
Please check for minor syntax issues, code below

Plan:
load
Company&[Brand Code]&[Market]&[Order Type Code]&[key_Date] as _key ,
Company as Plan_Company,
[Brand Code] as Plan_bc,
Market as Plan_market,
[Order Type Code] as Plan_ot,
[Sales Plan Amount] ,
[Cost Plan Amount] ,
[Sales Plan Quantity] ,
ApplyMap('PeriodMap', text([Year]) & '|' & text([Period])) as Plan_key_Date
resident F100;

Sales:
load
Company&[Brand Code]&[Market]&[Order Type Code]&[key_Date] as _key ,
[Company] as Sales_Company,
[Brand Code] as Sales_bc,
[Market] as Sales_market,
[Order Type Code] as Sales_ot,
[key_Date] as Sales_key_date
resident F200;

Link Table:
Load _key,
Plan_Company as Company ,
Plan_bc as [Brand Code] ,
Plan_market as Market ,
Plan_ot as [Order Type Code],
Plan_key_Date as key_Date
Resident Plan;
concatenate
Load _key,
Sales_Company as Company ,
Sales_bc as [Brand Code] ,
Sales_market as Market ,
Sales_ot as [Order Type Code],
Sales_key_Date as key_Date
Resident Sales;


Drop fields Plan_Company ,Plan_bc, Plan_market ,Plan_ot ,Plan_key_Date ,Sales_Company ,Sales_bc, Sales_market ,Sales_ot ,Sales_key_Date;