Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

psk180590
Contributor II

Min of a Value and Generate Missing Values

Hello All,

I have the following table :

Articel_CodeDate_FromCost
67601.01.20023,58
67601.03.20023,78
67601.03.20033,98
67601.07.20048,21
67601.07.20058,37
67601.02.20098,55
67601.02.20108,64
67601.02.20118,9
67601.02.20129,08
67601.02.20139,22
67615.01.20149,35
67601.02.20159,78
67601.02.20169,92
67615.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_CodeDate_FromCost
67601.01.20023,58
67601.03.20033,98
67601.07.20048,21
67601.07.20058,37
67601.07.20068,37
67601.07.20078,37
67601.07.20088,37
67601.02.20098,55
67601.02.20108,64
67601.02.20118,9
67601.02.20129,08
67601.02.20139,22
67615.01.20149,35
67601.02.20159,78
67601.02.20169,92
67601.02.20179,92
67615.01.201810

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Min of a Value and Generate Missing Values

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;

8 Replies
vishsaggi
Esteemed Contributor III

Re: Min of a Value and Generate Missing Values

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

psk180590
Contributor II

Re: Min of a Value and Generate Missing Values

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
Esteemed Contributor III

Re: Min of a Value and Generate Missing Values

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;

Capture.PNG

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

psk180590
Contributor II

Re: Min of a Value and Generate Missing Values

Unbenannt.PNG

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
Contributor II

Re: Min of a Value and Generate Missing Values

Hello All,

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

Thanks

Sai.

Arjunarao
Honored Contributor II

Re: Min of a Value and Generate Missing Values

MVP
MVP

Re: Min of a Value and Generate Missing Values

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
Esteemed Contributor III

Re: Min of a Value and Generate Missing Values

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