Help me on creating flags with last updated range(LOW/MEDIUM/HIGH)

[venkatasuresh]

Hi Experts,

Need your help on below Data flagging range(LOW/MEDIUM/HIGH)

Data i have as input :

LOAD * INLINE [

    Product_ID, Date

    SDN-09, 01/01/2008

    SDN-10, 20/11/2008

    SDN-10, 30/03/2009

    SDN-12, 02/07/2010

    SDN-11, 16/12/2010

    SDN-12, 10/01/2011

    SDN-11, 13/09/2012

     SDN-12, 29/11/2012

    SDN-11, 29/07/2013

    SDN-10,

    SDN-12, 15/12/2014

   ];

Obtain the date value from the column Date, calculate the time difference between the current date and the obtained Date, divide it by 365, round it up to one decimal place

Then  fallow below calculation if

Product_ID <= 3 years ------>Low

3 < Product_ID => 5  years---->Medium

Product_ID   > 5 years---->High

If Date is BLANK ------->High

Expected out put if we select any Product_ID we need to get the last updated time of the Product_ID with flag(High/Medium/Low)

Product_ID   Decimal        Flag

SDN-09--------9.9--------------High

SDN-10--------Blank-----------High

SDN-11--------4.3-------------- Medium

SDN-12--------2.9--------------low

Note:Decimal value not required to show in front end(Need to show only Product_ID and Flag)

Thanks,

Suresh V.V

[tresesco]

This?

Load

  *,

  If( Diff<=3*365, 'Low',If(Diff<=5*365, 'Medium', 'High')) as Flag;

LOAD *,

  Today()-Date as Diff,

  Round((Today()-Date)/365,0.1) as Decimal

  INLINE [

    Product_ID, Date

    SDN-09, 01/01/2008

    SDN-10, 20/11/2008

    SDN-10, 30/03/2009

    SDN-12, 02/07/2010

    SDN-11, 16/12/2010

    SDN-12, 10/01/2011

    SDN-11, 13/09/2012

    SDN-12, 29/11/2012

    SDN-11, 29/07/2013

    SDN-10,

    SDN-12, 15/12/2014

  ];

[venkatasuresh]

Hi tresesco,

Thank you for your quick response,

Here if we pick any one of the Product_ID we need to highlight  latest time stamp product_ID, and if the time stamp is null we need to highlight value

Ex:

[YoussefBelloum]

What is the object type you will use for this table? Tablebox ? Straight table ?

[venkatasuresh]

Hi youssefbelloum‌,

Thanks for response,

We are expecting on table box.

Thanks,

Suresh V.V

[YoussefBelloum]

I think you can't do conditional coloring on a table box, use a straight table if you can.

but you can right click on the objet ==> custom cell format, you will find customized coloring there, try it

[venkatasuresh]

Hi youssef.belloum‌ ,

Thanks For your response,

Im not expecting coloring,

If you select Product_ID SDN-12 from filter, Its showing four Decimal values(2.9,5.0,6.9,7.4) from Date range 2010 to 2014

In this cause we need to show only latest time stamp value 2.9

Thanks,

Suresh V.V