Can two resident tables be joined by link table ?

[claudialet]

 Here is my script . Sales table is very large. 

Sales, plan and link table does not show in the data model. Help !

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

Sales:

load

[Company],                           

[Brand Code]     ,  

[Market] ,                            

[Order Type Code]  ,

[key_Date]                        

resident Sales;

[Link Table]: Load

Company  & ' | ' & [Brand Code] & ' | ' & Market & ' | ' & [key_Date] as [%Key Field],

 [Company] ,                        

[Brand Code] ,     

[Market] ,                            

[Order Type Code]  ,

[key_Date]                        

resident Sales;

CONCATENATE  ([Link Table ])

Load

            Company  & ' | ' & [Brand Code] & ' | ' & Market & ' | ' & [key_Date] as [%Key Field],

               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;

[Gysbert_Wassenaar]

[%Key Field] needs to exist not only in the link table, but also in the Sales and Plan tables. Otherwise those tables won't be linked to the link table. After creating the link table the fields [Company], [Brand Code], [Market], [Order Type Code] and [key_Date] need to be dropped from the Sales and Plan tables.

[claudialet]

What is correct sentence drop the fields from a resident table ?

Drop fieldname ;

resident table;

drop table;   

[Gysbert_Wassenaar]

See the online help: https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegu...

[claudialet]

 This is complicated.  Right now the 2 resident table are concatenated.   I need to separate them and create link 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 [Save key_Date]
resident Plan;

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

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;

[asinha1991]

what are you trying to achieve?
two tables sales and plan in data model joined by a link table?

[claudialet]

Yes . See  here for requirements: 

https://community.qlik.com/t5/New-to-QlikView/How-to-separate-2-concatenated-resident-tables/m-p/157...

[asinha1991]

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;