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.
Yep, I haven�t still found the solution.
Your code is the key but I'm struggling extrapolating that to my code.
My code:
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";
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 (Year & Month & [Product Type] & Shop);
Load
Year & Month & [Product Type] & Shop as Key,
Year, Month, [Product Type], Shop
Resident TempKey1;
Drop Table TempKey1;
Drop Field Key;
As you can see, I import a table from SQL and I have few more columns, but the important ones are Year, Month, Product Type, Shop and Sales.
When I execute this code I get 0 errors but it does nothing.
Try this
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
Year & Month & [Product Type] & Shop as Key,
Year, Month, [Product Type], Shop
Resident TempKey1;
Drop Table TempKey1;
Drop Field Key;
I get Script Error for every field of the line you added
"...
"Size",
Year & Month & Shop & [Product Type] as Key
;
...
The error:
Field Not found - <Year>
SQL SELECT *
FROM bmw.db0.'"Date Base"'
I think its because the name in SQL is YEAR not Year.
If I delete the fields that arent Year, Month, Product Type, Shop and Sales, the code I showed you works.
Sorry use this..
YEAR & Date(Date#(MONTH,'MM'),'MMMM') & SHOP & [Product Type] as Key
Many thanks,
The solution was to add every field to that expression.
Sorry but I have another question, I want to add a column call List. This list will have the value "yes" when the shops are london and Paris.
So I tried this:
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*, 'Yes' as List where Shop= ' London' or Shop = 'Paris';
Load
Year & Month & [Product Type] & Shop as Key,
Year, Month, [Product Type], Shop
Resident TempKey1;
Drop Table TempKey1;
Drop Field Key;
It runs fine, but some values created with your code dissapear. Some still remain. I don't know why...
Create another thread please.
Because you are only loading London and Paris shops. So all other records which are not present in Data table and are created using below code and not having Shop = London or Paris will be ignored.
Load Distinct Year Resident Data;
Join
Load Distinct Month Resident Data;
join
Load Distinct [Product Type] Resident Data;
Join
Load Distinct Shop Resident Data;
then how can I include all?
Load*, 'Yes' as List where Shop= ' London' or Shop = 'Paris';
Load*, 'NO' as List where not Shop= ' London' or Shop = 'Paris';
that doesnt work...