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.

[MK_QSL]

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;

[sdaniels0606]

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