Min of a Value and Generate Missing Values

[psk180590]

Hello All,

I have the following table :

Articel_Code	Date_From	Cost
676	01.01.2002	3,58
676	01.03.2002	3,78
676	01.03.2003	3,98
676	01.07.2004	8,21
676	01.07.2005	8,37
676	01.02.2009	8,55
676	01.02.2010	8,64
676	01.02.2011	8,9
676	01.02.2012	9,08
676	01.02.2013	9,22
676	15.01.2014	9,35
676	01.02.2015	9,78
676	01.02.2016	9,92
676	15.01.2018	10

And i have two requirements from this:

1. The Year 2002 is repeated twice, i only want the cost against the Minimum(First Date) of Date_From (i.e, 3,58)

2. The cost is missing against the Years 2006,2007,2008 and 2017. Here i would like to replace it with the value from previous Year Cost.

Expected Result:

Articel_Code	Date_From	Cost
676	01.01.2002	3,58
676	01.03.2003	3,98
676	01.07.2004	8,21
676	01.07.2005	8,37
676	01.07.2006	8,37
676	01.07.2007	8,37
676	01.07.2008	8,37
676	01.02.2009	8,55
676	01.02.2010	8,64
676	01.02.2011	8,9
676	01.02.2012	9,08
676	01.02.2013	9,22
676	15.01.2014	9,35
676	01.02.2015	9,78
676	01.02.2016	9,92
676	01.02.2017	9,92
676	15.01.2018	10

I tried getting the min of value with FirstSortedValue Funaction and then Generating the Missing Values using AutoNumberHash.

But, it didn't get the expected result.

TIA!!

Changed the Expected Result

[vishsaggi]

How about the missing costs for the Years 2006, 2007, 2008?

[psk180590]

Good point, actually i'm interested only for the values from Year 2012.

But, the same logic for Year 2017 should also work for all missing Years. The Years 2006, 2007 and 2008 should have the cost from Year 2005.

[vishsaggi]

Check this?

Missing:

LOAD *, Year(Date(DateID)) AS Year;

LOAD Articel_Code,

     Num#(Date#(Date_From, 'M/D/YYYY')) AS DateID,

     Date_From,

     Cost

FROM

Peek.xlsx

(ooxml, embedded labels, table is Tabelle1);

RIGHT JOIN(Missing)

Calendar:

LOAD TempYear as Year;

LOAD

date(MinYear + IterNo()) AS TempYear

,MaxYear // Used in InYearToDate() above, but not kept

WHILE MinYear + IterNo() <= MaxYear;

LOAD min(FieldValue('Year', RecNo())-1) AS MinYear,

     max(FieldValue('Year', RecNo())) AS MaxYear

AutoGenerate(FieldValueCount('Year'));

Final:

LOAD Year,

     Articel_Code,

    //Date_From,

    // Cost,

     DateID,

     IF(Len(Trim(Articel_Code)) = 0, Peek('Articel_Code',1), Articel_Code) AS ArticleFlag,

     IF(Len(Trim(Cost)) = 0, Peek('Cost'), Cost) AS Cost,

     IF(Len(Trim(Date_From)) = 0, Peek('Date_From'), Date_From) AS Date_From

Resident Missing

Order By Year;

Drop Table Missing;

Add year and ArticleFlag as Dim and exprs accordingly as show above using straight table.

[psk180590]

Hello Vishwarath,

I'm somehow not getting the desired result, above is an screen shot of the straight table. Can you share the qvw please?

[psk180590]

Hello All,

Can anyone help me on this. Still not able to figure out the reason.

Thanks

Sai.

[qlikviewwizard]

Hi,

Please check this How to populate a sparsely populated field and  Generating Missing Data In QlikView

[sunny_talwar · Accepted solution]

May be try this

Table:

LOAD Articel_Code,

    Date_From,

    Year(Date_From) as Year,

    Cost

FROM

[..\..\Downloads\Peek.xlsx]

(ooxml, embedded labels, table is Tabelle1);

TempTable:

LOAD MinYear + IterNo() - 1 as Year,

Date(MakeDate(MinYear + IterNo() - 1, 1, 1)) as Date_From

While MinYear + IterNo() - 1 <= MaxYear;

LOAD Min(Year) as MinYear,

Max(Year) as MaxYear

Resident Table;

Join (TempTable)

LOAD Distinct Articel_Code

Resident Table;

Concatenate (Table)

LOAD DISTINCT *

Resident TempTable;

DROP Table TempTable;

Right Join (Table)

LOAD Articel_Code,

Year,

If(Count(Year) > 1, Min(If(Len(Trim(Cost)) > 0, Date_From)), Min(Date_From)) as Date_From

Resident Table

Group By Articel_Code, Year;

FinalTable:

NoConcatenate

LOAD Articel_Code,

Date_From,

If(Cost = 0, Peek('Cost'), Cost) as Cost;

LOAD Articel_Code,

Date_From,

Sum(Cost) as Cost

Resident Table

Group By Articel_Code, Date_From

Order By Articel_Code, Date_From;

DROP Table Table;

[vishsaggi]

Will have to look into my folder. Will send it tomorrow.