Forward Filling of Values

[Rvr123]

Hi Qlik Experts,

Please refer the data which I have attached 'Input_Output File.xlsx'.

Example:
This is my input data.

product_brand_name	product_name	product_ndc	date_year_month	Sum of effective_wac_price
Vac2387	200MG/10ML	13601	2017-01	934.440857
Vac2387	200MG/10ML	13601	2017-02	948.73
Vac2387	200MG/10ML	13601	2017-04	1011
Vac2387	200MG/10ML	13601	2017-05	948.73
Vac2387	200MG/10ML	13601	2017-06	948.73
Vac2387	200MG/10ML	13601	2017-07	981.127746
Vac2387	200MG/10ML	13601	2017-10	994.28
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-01	50
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-02	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-06	52
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-08	53

Expected Output:

product_brand_name	product_name	product_ndc	date_year_month	Sum of effective_wac_price
Vac2387	200MG/10ML	13601	2017-01	934.440857
Vac2387	200MG/10ML	13601	2017-02	948.73
Vac2387	200MG/10ML	13601	2017-03	948.73
Vac2387	200MG/10ML	13601	2017-04	1011
Vac2387	200MG/10ML	13601	2017-05	948.73
Vac2387	200MG/10ML	13601	2017-06	948.73
Vac2387	200MG/10ML	13601	2017-07	981.127746
Vac2387	200MG/10ML	13601	2017-08	981.127746
Vac2387	200MG/10ML	13601	2017-09	981.127746
Vac2387	200MG/10ML	13601	2017-10	994.28
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-01	50
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-02	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-03	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-04	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-05	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-06	52
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-07	52
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-08	53
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-09	53
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-10	53

Please provide me the solution in Qlik.

I have tried firstly Master calendar , secondly used peek function. Im provide the script here as well.

Data:

LOAD 

 Date(Date#(date_year_month,'YYYY-MM'),'YYYY-MM') as Date,

product_brand_name as Country, 

     //product_name, 

     product_ndc  as ID, 

     [Sum of effective_wac_price] as Value

FROM

[C:\Users\rvr1\Downloads\WAC File With Rishi Dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

MinMax:

LOAD 

    min(Date(Date#(Date,'YYYY-MM'),'YYYY-MM')) as MinDate,

    max(Date(Date#(Date,'YYYY-MM'),'YYYY-MM')) as MaxDate

RESIDENT Data;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

Drop Table MinMax;

TempCal:

LOAD

    Date($(vMinDate) + Rowno() - 1) as Tempdate

AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

///----2nd set

Date: 

Load Date(Date#(Tempdate),'YYYY-MM') as Date2 Resident TempCal;

Drop Table TempCal;

Date2:

Load Date2 as Date, 

0 as Value

Resident Date

Where not exists (Date, Date2);

Drop Table Date;

Join (Date2)

Dist_Country:

Load Distinct Country Resident Data;

Concatenate (Data)

Load * Resident Date2;

Drop Table Date2;

Temp:

Load 

Date, Country, ID, Value,

if(peek(Country)=Country, if(Value = 0, Peek(Value2),Value),Value) as Value2

Resident Data

Order by Country, Date;

Drop Table Data;

Exit SCRIPT;

Please provide me the script for the output. Its very challenging for me to get the output.

I need your help guys.

Thanks in Advance Heartfully.

[SRA · Accepted solution]

Hi,

You can try this : 

T_Data:

Load *

,   product_brand_name & product_name & product_ndc                              as Key

, Date(MonthEnd(MakeDate(Left(date_year_month,4), Right(date_year_month,2)))) as Date

Inline [

product_brand_name, product_name, product_ndc, date_year_month, Sum_of_effective_wac_price

Vac2387, 200MG/10ML, 13601, 2017-01, 934.440857

Vac2387, 200MG/10ML, 13601, 2017-02, 948.73

Vac2387, 200MG/10ML, 13601, 2017-04, 1011

Vac2387, 200MG/10ML, 13601, 2017-05, 948.73

Vac2387, 200MG/10ML, 13601, 2017-06, 948.73

Vac2387, 200MG/10ML, 13601, 2017-07, 981.127746

Vac2387, 200MG/10ML, 13601, 2017-10, 994.28

Vac_5590, VIAL WET SD 200MG/10ML, 13602, 2017-01, 50

Vac_5590, VIAL WET SD 200MG/10ML, 13602, 2017-02, 51

Vac_5590, VIAL WET SD 200MG/10ML, 13602, 2017-06, 52

Vac_5590, VIAL WET SD 200MG/10ML, 13602, 2017-08, 53

];

T_Interval:

Load

Key as Key

,   if(Key<>Previous(Key), Date(MakeDate(2999,12,31)), Date(Previous(Date)-1)) as To_Date

, Date   as From_Date

, Sum_of_effective_wac_price as Sum_of_effective_wac_price 

Resident T_Data

Order by Key, Date Desc;

T_MinMax:

Load

  Min(Date#(date_year_month, 'YYYY-MM')) AS Min_date_year_month,

    Max(Date#(date_year_month, 'YYYY-MM')) AS Max_date_year_month

Resident T_Data;

LET vMin_Min_date_year_month = Num(Peek('Min_date_year_month', 0, 'T_MinMax'));

LET vMax_Max_date_year_month = Num(Peek('Max_date_year_month', 0, 'T_MinMax'));

Drop Table T_MinMax;

// Init table with dates (End month by default)

F_Data:

Load

Date(MonthEnd(AddMonths($(vMin_Min_date_year_month), IterNo() - 1))) as Tmp_Date

,   Text(Date(AddMonths($(vMin_Min_date_year_month), IterNo() - 1), 'YYYY-MM')) as date_year_month

Autogenerate 1

While AddMonths($(vMin_Min_date_year_month), IterNo() - 1) <= $(vMax_Max_date_year_month);;

// Add dimensions for each dates

Left Join(F_Data)

Load Distinct

Key

, product_brand_name

,   product_name

,   product_ndc

Resident T_Data;

Drop Table T_Data;

Left Join(F_Data)

IntervalMatch(Tmp_Date, Key)

Load

From_Date

, To_Date    

, Key

Resident T_Interval;

Left Join(F_Data)

Load

From_Date

, To_Date    

, Key

, Sum_of_effective_wac_price

Resident T_Interval;

Drop Fields From_Date, To_Date, Key, Tmp_Date From F_Data;

Drop Table T_Interval;

You will get :

Regards,

SRA

[DanielC]

Hi

I have found this solution too:

Input_Table:

LOAD

AutoNumberHash256(date_year_month&product_brand_name) as Key,

product_brand_name,

        product_name,

        product_ndc,

        date_year_month,

       "Sum of effective_wac_price"

FROM [lib://DataFiles/Input_Output File .xlsx]

(ooxml, embedded labels, table is Input);

MinMax:

LOAD 

         Min(Date(Date#(date_year_month,'YYYY-MM'),'YYYY-MM')) as MinDate,

         Max(Date(Date#(date_year_month,'YYYY-MM'),'YYYY-MM')) as MaxDate

RESIDENT Input_Table;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

DROP TABLE MinMax;

TempCal:

LOAD

         Year($(vMinDate) + Rowno() - 1) &'-'& num(month($(vMinDate) + Rowno() - 1),'00') as date_year_month

AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

JOIN(TempCal)

LOAD

product_brand_name,

        product_name,

        product_ndc

RESIDENT Input_Table;

Full_Key:

LOAD  *,

           AutoNumberHash256(date_year_month&product_brand_name) as Key1

RESIDENT TempCal;

DROP TABLE TempCal;

Concatenate(Input_Table)

LOAD  *

RESIDENT Full_Key

WHERE not Exists(Key,Key1);

DROP TABLE Full_Key;

Output_Table:

NoConcatenate

LOAD 

date_year_month,

        product_brand_name,

product_name,

        product_ndc,

        If(len("Sum of effective_wac_price")=0, peek("Sum of effective_wac_price"), "Sum of effective_wac_price") as "Sum of effective_wac_price"

RESIDENT Input_Table

WHERE len(If(len("Sum of effective_wac_price")=0, peek("Sum of effective_wac_price"), "Sum of effective_wac_price"))>0

ORDER BY product_brand_name, product_name, product_ndc, date_year_month;

DROP TABLE Input_Table;

I hope it helps.

Kind Regards

Daniel

 

[Rvr123]

Hi Daniel,

Thanks its really helpful. Its giving the expected output.

But there is a case, in my input excel file if you see, both product_brand_name have the same date_year_month start. (That is product_brand_name of Vac2387 and Vac_5590 have 2017-01 is the start date).

but my 3rd product_brand_name Vac_8989 have the start date of 2017-05. but When I ran your code, Im getting values populated on 2017-01 to 2017-04 for this product which is picking values from previous product_brand_name. But I expect the start date of this product_brand_name to be from 2017-05 only. (Please refer my input and expected output table below)

Input:

product_brand_name	product_name	product_ndc	date_year_month	Sum of effective_wac_price
Vac2387	200MG/10ML	13601	2017-01	934.440857
Vac2387	200MG/10ML	13601	2017-02	948.73
Vac2387	200MG/10ML	13601	2017-04	1011
Vac2387	200MG/10ML	13601	2017-05	948.73
Vac2387	200MG/10ML	13601	2017-06	948.73
Vac2387	200MG/10ML	13601	2017-07	981.127746
Vac2387	200MG/10ML	13601	2017-10	994.28
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-01	50
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-02	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-06	52
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-08	53
Vac_8989	300MG/50ML	15666	2017-05	70
Vac_8989	300MG/50ML	15666	2017-09	80

Expected Output:

product_brand_name	product_name	product_ndc	date_year_month	Sum of effective_wac_price
Vac2387	200MG/10ML	13601	2017-01	934.440857
Vac2387	200MG/10ML	13601	2017-02	948.73
Vac2387	200MG/10ML	13601	2017-03	948.73
Vac2387	200MG/10ML	13601	2017-04	1011
Vac2387	200MG/10ML	13601	2017-05	948.73
Vac2387	200MG/10ML	13601	2017-06	948.73
Vac2387	200MG/10ML	13601	2017-07	981.127746
Vac2387	200MG/10ML	13601	2017-08	981.127746
Vac2387	200MG/10ML	13601	2017-09	981.127746
Vac2387	200MG/10ML	13601	2017-10	994.28
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-01	50
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-02	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-03	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-04	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-05	51
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-06	52
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-07	52
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-08	53
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-09	53
Vac_5590	VIAL WET SD,200MG/10ML	13602	2017-10	53
Vac_8989	300MG/50ML	15666	2017-05	70
Vac_8989	300MG/50ML	15666	2017-06	70
Vac_8989	300MG/50ML	15666	2017-07	70
Vac_8989	300MG/50ML	15666	2017-08	70
Vac_8989	300MG/50ML	15666	2017-09	80

[Rvr123]

Hi Daniel,

Thanks for your code. It was really helpful.

The edge case I'm having is, 
Assume 'Vac_8989' (from product_brand_name column) product newly coming from the start date of '2017-05' (from date_year_month column). Since we have a minimum date of data is '2017-01', the code which you have provided, is populating values for 2017-01 to 2017-04(means its picking previous product_brand_name values).

Please refer the attached file which has my

1. New input in 'Input' sheet

2. Current Output in 'Current Output' sheet

3. Expected output in 'Required Output'

please provide me code for my requirements.

Thanks in advance

[DanielC · Accepted solution]

Hi

I have adjusted the script for the new requirement to take into account the products that didn't start on 2017-01.

However, I have a question, in your excel the required output for Vac_8989 doesn't have 2017-10. Should it be like this or should this line also be included?

I mean, with the script below the 2017-10 is included since I assumed it should be there like the other products and because then the modification is minimum. Removing it will be a bit trickier. So, let me know.

Input_Table:

LOAD AutoNumberHash256(date_year_month&product_brand_name) as Key,

    product_brand_name,

    product_name,

    product_ndc,

    date_year_month,

    date_year_month as date_year_month_original,

    "Sum of effective_wac_price"

FROM [lib://DataFiles/Input_Output File  (1).xlsx]

(ooxml, embedded labels, table is Input);

MinMax:

LOAD 

    Min(Date(Date#(date_year_month,'YYYY-MM'),'YYYY-MM')) as MinDate,

    Max(Date(Date#(date_year_month,'YYYY-MM'),'YYYY-MM')) as MaxDate

RESIDENT Input_Table;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

DROP TABLE MinMax;

TempCal:

LOAD

    Year($(vMinDate) + Rowno() - 1) &'-'& num(month($(vMinDate) + Rowno() - 1),'00') as date_year_month

AUTOGENERATE $(vMaxDate) - $(vMinDate) +1;

JOIN(TempCal)

LOAD

product_brand_name,

    product_name,

    product_ndc,

    date_year_month_original

RESIDENT Input_Table;

Full_Key:

LOAD *,

    AutoNumberHash256(date_year_month&product_brand_name) as Key1

RESIDENT TempCal

where date_year_month>=date_year_month_original;

DROP TABLE TempCal;

Concatenate(Input_Table)

LOAD  *

RESIDENT Full_Key

WHERE not Exists(Key,Key1);

DROP TABLE Full_Key;

Output_Table:

NoConcatenate

LOAD 

date_year_month,

    product_brand_name,

product_name,

    product_ndc,

    If(len("Sum of effective_wac_price")=0, peek("Sum of effective_wac_price"), "Sum of effective_wac_price") as "Sum of effective_wac_price"

RESIDENT Input_Table

WHERE len(If(len("Sum of effective_wac_price")=0, peek("Sum of effective_wac_price"), "Sum of effective_wac_price"))>0

ORDER BY product_brand_name,product_name,product_ndc,date_year_month;

DROP TABLE Input_Table;

Kind Regards