Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
PFA
Thanks.
But i need to set min YearMonth as 201301, max - today YearMonth
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;
Thanks!!!!I