topic Re: Help me on creating flags with last updated range(LOW/MEDIUM/HIGH) in QlikView

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

venkatasuresh — Wed, 25 Nov 2020 16:16:04 GMT

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

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

tresB — Wed, 15 Nov 2017 09:17:08 GMT

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

];

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

venkatasuresh — Wed, 15 Nov 2017 09:38:44 GMT

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:

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

YoussefBelloum — Wed, 15 Nov 2017 10:16:07 GMT

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

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

venkatasuresh — Wed, 15 Nov 2017 10:30:59 GMT

Hi youssefbelloum‌,

Thanks for response,

We are expecting on table box.

Thanks,

Suresh V.V

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

YoussefBelloum — Wed, 15 Nov 2017 10:50:25 GMT

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

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

venkatasuresh — Wed, 15 Nov 2017 11:25:37 GMT

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