Create new rows when there are no sales

[sdaniels0606]

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.

[sdaniels0606]

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.

[MK_QSL]

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;

[sdaniels0606]

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.

[sdaniels0606]

If I delete the fields that arent Year, Month, Product Type, Shop and Sales, the code I showed you works.

[MK_QSL · Accepted solution]

Sorry use this..

YEAR & Date(Date#(MONTH,'MM'),'MMMM') & SHOP & [Product Type] as Key

[sdaniels0606]

Many thanks,

The solution was to add every field to that expression.

[sdaniels0606]

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...

[MK_QSL]

Create another thread please.

[MK_QSL]

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;

[sdaniels0606]

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...