Find new field based on previous fields.. ?

    Hi All,

    We have an urgent QlikView requirement . We have a table having Some fields. Two are Date fields Date1 & Date2. In respect of Date1 field there is a Value1 field. We need to derive the Value2 with respect to date2. Value2 is depend on 3 fields Customer, Date1 and Value1.

    Example in Excel Sheet:: For C1 ,Date1 is 01/01/2016 and Value is 100.Therefore for Date2 we need to derive the value2.

    Logic is that if for Same Customer Date1 <=Date2 then Value1 will be the value2.

    For Column 1 Value of Value2 will be 100 because Date1=Date2.

    Other case Column2 ::Date1 > Date2 ,In this case we will see the upper value of Date1 if this Date <= Date2 then Value of Value1 for that will  be the Value of Value2. For Column2 Value2 will be 100.

    Same for column3 Value2 will be 100 because Date1> date2. So we are always assuming the value of value2 ,according to date2 where Date1<=Date2.

      If not then we assuming previous value. If the previous Date is also large then we will check upper of the previous value. This is our overall logic.

     

     

    Regards

    Munna

     

    Hi,

    Can you provide the excel formula which you explained above?

     

    If I can know the formula i will create in Qlikview as well... I need Qlikview script coding..

     

    I did some extend of code but didn't get correct out put. please let me know as soon as.

     

    Thanks in Advance

    MM

     

    May be this:

     

    Table:

    LOAD Customer,

        Date1,

        Value1,

        Date2,

        Value2_Temp1,

        Value2_Temp2,

        Alt(Value2_Temp1, Value2_Temp2) as Value2;

    LOAD *,

      If(Peek('Flag') = 1, Peek('Value2_Temp1'), If(Date2 >= Peek('Date1'), Peek('Value1'), Peek('Value2_Temp2'))) as Value2_Temp2;

    LOAD Customer,

        Date1,

        Value1,

        Date2,

        If(Date1 <= Date2, Value1) as Value2_Temp1,

        If(Date1 <= Date2, 1) as Flag

    FROM

    [Test (8).xlsx]

    (ooxml, embedded labels, table is Sales);

     

    DROP Fields Value2_Temp1, Value2_Temp2;

     

    Capture.PNG

     

    Thanks Sunny..

     

    This document was generated from the following discussion: Find new field based on previous fields.. ?