Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
What is correct sentence drop the fields from a resident table ?
Drop fieldname ;
resident table;
drop table;
See the online help: https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegu...
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;
Yes . See here for requirements:
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;