8 Replies Latest reply: Jul 11, 2018 11:40 PM by Vishwarath Nagaraju

# 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

• ###### Re: Min of a Value and Generate Missing Values

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

• ###### 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.

• ###### Re: Min of a Value and Generate Missing Values

Check this?

Missing:

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:

date(MinYear + IterNo()) AS TempYear

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

WHILE MinYear + IterNo() <= MaxYear;

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

AutoGenerate(FieldValueCount('Year'));

Final:

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

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

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

• ###### 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.

• ###### Re: Min of a Value and Generate Missing Values

May be try this

Table:

Date_From,

Year(Date_From) as Year,

Cost

FROM

(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;

Max(Year) as MaxYear

Resident Table;

Join (TempTable)

Resident Table;

Concatenate (Table)

Resident TempTable;

DROP Table TempTable;

Right Join (Table)

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

Date_From,

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