Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andreyfcdk91
New Contributor III

Fill missing values for every YearMonth

Good day!

In my model i load following fields from excel:

ChainContractorEANArticleYearMonthSum

Result table from source:

     

ChainContractorEANArticleYearMonthSum
AuchanАшан14563420130245
AuchanАшан14563420130356
AuchanАшан14563420130632
AuchanАшан145634201309543
AuchanАшан145634201401324
AuchanАшан14563420140265
AuchanАшан145634201403342
AuchanАшан145634201404324
AuchanАшан14563420140554
AuchanАшан145634201406324
AuchanАшан14563420140789
AuchanАшан14563420150123
AuchanАшан14563420150224
AuchanАшан1456342015032342
AuchanАшан145634201506324

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:

   

ChainContractorEANArticleYearMonthSum
AuchanАшан145634201301-
AuchanАшан14563420130245
AuchanАшан14563420130356
AuchanАшан145634201304-
AuchanАшан145634201305-
AuchanАшан14563420130632
AuchanАшан145634201307-
AuchanАшан145634201308-
AuchanАшан145634201309543
AuchanАшан145634201310-
AuchanАшан145634201311-
AuchanАшан145634201312-
AuchanАшан145634201401324
AuchanАшан14563420140265
AuchanАшан145634201403342
AuchanАшан145634201404324
AuchanАшан14563420140554
AuchanАшан145634201406324
AuchanАшан14563420140789
AuchanАшан145634201408-
AuchanАшан145634201409-
AuchanАшан145634201410-
AuchanАшан145634201411-
AuchanАшан145634201412-
AuchanАшан14563420150123
AuchanАшан14563420150224
AuchanАшан1456342015032342
AuchanАшан145634201504-
AuchanАшан145634201505-
AuchanАшан145634201506324
AuchanАшан145634201507-

Model and source file in attachment.

Thanks.

1 Solution

Accepted Solutions
MVP
MVP

Re: Fill missing values for every 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;

4 Replies
MVP
MVP

Re: Fill missing values for every YearMonth

PFA

andreyfcdk91
New Contributor III

Re: Fill missing values for every YearMonth

Thanks.

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

MVP
MVP

Re: Fill missing values for every 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;

andreyfcdk91
New Contributor III

Re: Fill missing values for every YearMonth

Thanks!!!!I

Community Browser