Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
psk180590
Creator III
Creator III

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
sunny_talwar

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;

View solution in original post

8 Replies
vishsaggi
Champion III
Champion III

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

psk180590
Creator III
Creator III
Author

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
Champion III
Champion III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hello All,

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

Thanks

Sai.

qlikviewwizard
Master II
Master II

sunny_talwar

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
Champion III
Champion III

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