Fill missing values for every YearMonth

[Anonymous]

Good day!

In my model i load following fields from excel:

Chain

Contractor

EAN

Article

YearMonth

Sum

Result table from source:

Chain	Contractor	EAN	Article	YearMonth	Sum
Auchan	Ашан1	456	34	201302	45
Auchan	Ашан1	456	34	201303	56
Auchan	Ашан1	456	34	201306	32
Auchan	Ашан1	456	34	201309	543
Auchan	Ашан1	456	34	201401	324
Auchan	Ашан1	456	34	201402	65
Auchan	Ашан1	456	34	201403	342
Auchan	Ашан1	456	34	201404	324
Auchan	Ашан1	456	34	201405	54
Auchan	Ашан1	456	34	201406	324
Auchan	Ашан1	456	34	201407	89
Auchan	Ашан1	456	34	201501	23
Auchan	Ашан1	456	34	201502	24
Auchan	Ашан1	456	34	201503	2342
Auchan	Ашан1	456	34	201506	324

I need make some changes in script level to fill missing values for every Chain, Contractor, EAN, Article for YearMonth from 201301 to last actual YearMonth (calculated by reload time of model)

Necessary table:

Chain	Contractor	EAN	Article	YearMonth	Sum
Auchan	Ашан1	456	34	201301	-
Auchan	Ашан1	456	34	201302	45
Auchan	Ашан1	456	34	201303	56
Auchan	Ашан1	456	34	201304	-
Auchan	Ашан1	456	34	201305	-
Auchan	Ашан1	456	34	201306	32
Auchan	Ашан1	456	34	201307	-
Auchan	Ашан1	456	34	201308	-
Auchan	Ашан1	456	34	201309	543
Auchan	Ашан1	456	34	201310	-
Auchan	Ашан1	456	34	201311	-
Auchan	Ашан1	456	34	201312	-
Auchan	Ашан1	456	34	201401	324
Auchan	Ашан1	456	34	201402	65
Auchan	Ашан1	456	34	201403	342
Auchan	Ашан1	456	34	201404	324
Auchan	Ашан1	456	34	201405	54
Auchan	Ашан1	456	34	201406	324
Auchan	Ашан1	456	34	201407	89
Auchan	Ашан1	456	34	201408	-
Auchan	Ашан1	456	34	201409	-
Auchan	Ашан1	456	34	201410	-
Auchan	Ашан1	456	34	201411	-
Auchan	Ашан1	456	34	201412	-
Auchan	Ашан1	456	34	201501	23
Auchan	Ашан1	456	34	201502	24
Auchan	Ашан1	456	34	201503	2342
Auchan	Ашан1	456	34	201504	-
Auchan	Ашан1	456	34	201505	-
Auchan	Ашан1	456	34	201506	324
Auchan	Ашан1	456	34	201507	-

Model and source file in attachment.

Thanks.

[maxgro]

PFA

[Anonymous]

Thanks.

But i need to set min YearMonth as 201301, max - today YearMonth

[maxgro · Accepted solution]

Sales:

LOAD

  Chain, Contractor, EAN, Article, Sum,

  date(date#(YearMonth & '01', 'YYYYMMDD'), 'YYYYMM') as YearMonth

  FROM

Missing.xlsx

(ooxml, embedded labels, table is Sheet1);

let vmin=num(makedate(2013,1,1));

let vmax=num(MonthStart(Today()));

FinalSales:

NoConcatenate load

  Chain, Contractor, EAN, Article,

  date(AddMonths($(vmin), IterNo()-1), 'YYYYMM') as YearMonth

Resident Sales

while AddMonths($(vmin), IterNo()-1) <= $(vmax);

left join (FinalSales)

load Chain, Contractor, EAN, Article, YearMonth, Sum

Resident Sales;

DROP Table Sales;

[Anonymous]

Thanks!!!!I