topic Re: Min of a Value and Generate Missing Values in QlikView

Min of a Value and Generate Missing Values

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Min of a Value and Generate Missing Values

vishsaggi — Tue, 19 Jun 2018 15:19:55 GMT

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

Re: Min of a Value and Generate Missing Values

psk180590 — Tue, 19 Jun 2018 15:23:05 GMT

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.

Re: Min of a Value and Generate Missing Values

vishsaggi — Tue, 19 Jun 2018 16:24:11 GMT

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.

Re: Min of a Value and Generate Missing Values

psk180590 — Wed, 20 Jun 2018 08:14:28 GMT

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?

Re: Min of a Value and Generate Missing Values

psk180590 — Wed, 11 Jul 2018 09:32:39 GMT

Hello All,

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

Thanks

Sai.

Re: Min of a Value and Generate Missing Values

qlikviewwizard — Wed, 11 Jul 2018 09:48:50 GMT

Hi,

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

Re: Min of a Value and Generate Missing Values

sunny_talwar — Wed, 11 Jul 2018 11:20:29 GMT

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;

Re: Min of a Value and Generate Missing Values

vishsaggi — Thu, 12 Jul 2018 03:40:29 GMT

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