Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a date base which have data of the sales from different shops. The columns are Year, Month, Product Type, Shop and Sales.
When a shop hasn't sold anything of a Product Type in a month there is no record of it.
So I need to add/create a new row that includes the Month, the Date, the Product Type, the Shop and the sales which are 0 €.
How can I do that? I am pretty lost. I read https://community.qlik.com/docs/DOC-3786 but I didn't make any progress.
Thanks in advance.
Concatenate(Data)
Load
Year & Month & [Product Type] & Shop as Key,
Year, Month, [Product Type], Shop,
Resident TempKey1;
Left Join (Data)
Load Distinct Key, Shop, If(Match(Shop,'London','Paris'),'Yes','No') as List Resident Data;
Drop Table TempKey1;
Drop Field Key;
Like this works, thank you.
Data:
LOAD L,
"DISTRIB_PARTNER_OID",
SHOP as "Shop",
"PART_NO_ATLAS",
"Product Type",
"PART_DESC_ATLAS",
Sales,
PVP,
"PVP_REC",
VIN7,
"PAC_MONTHS",
"YEAR" as "Year",
//"MES" as "Mes",
Date(Date#(MONTH,'MM'),'MMMM') as Month,
"MONTHS_OLD",
ESERIE,
SERIE,
"DESC_MODELO",
"DATA_SOURCE",
List,
"Short List",
"Size"
YEAR & MONTH & SHOP & "Product Type" as Key
;
SQL SELECT *
FROM bmw.dbo."'Date Base$'";
TempKey1:
Load Distinct Year Resident Data;
Join
Load Distinct Month Resident Data;
join
Load Distinct [Product Type] Resident Data;
Join
Load Distinct Shop Resident Data;
Concatenate(Data)
Load*, 0 as Sales where Not Exists (Key);
Load*, If(Match(Shop,'London','Paris'),'Yes','No') as List;
Load
Year & Month & [Product Type] & Shop as Key,
Year, Month, [Product Type], Shop
Resident TempKey1;
Drop Table TempKey1;
Drop Field Key;