Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

swatitomar
New Contributor III

Max data on the basis of field

Hi Team,

Want to find  the max date on the basis of type Del and Serial no.

Like to show only max Del Data .

Input:

QtyTypeDateSerial_noDealer
1Del13/05/2017A1D01
1Delrt15/05/2017A1D01
1Del12/08/2017A2D02
1Del13/08/2017A3D03
1Delrt14/05/2017A3D03
1Del15/08/2017A3D04
1Del16/08/2017A4D05
1Delrt16/08/2017A4D05
1Del17/08/2017A4D05
1Del18/08/2017A5D06
1Delrt19/08/2017A5D06
1Del19/08/2017A5D06

  output

QtyTypeDateSerial_noDealer
1Del12/08/2017A2D02
1Del15/08/2017A3D03
1Del17/08/2017A4D05
1Del19/08/2017A5D06

Can anyone provide the solution.

Thanks in Advance..

1 Solution

Accepted Solutions
MVP
MVP

Re: Max data on the basis of field

May be try this

Table:

LOAD * INLINE [

    Qty, Type, Date, Serial, Dealer

    1, Del, 13/05/2016, A1, D01

    1, Delrt, 15/05/2016, A1, D01

    1, Del, 12/08/2017, A1, D01

    1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/05/2018, A3, D04

    1, Del, 16/08/2017, A4, D05

    1, Delrt, 16/08/2017, A4, D05

    1, Del, 17/08/2017, A4, D05

    1, Del, 18/08/2017, A5, D06

    1, Delrt, 19/08/2017, A5, D06

    1, Del, 19/08/2017, A5, D06

    1, Del, 13/05/2017, A6, D01

    1, Delrt, 15/06/2017, A6, D01

];


Right Join (Table)

LOAD Serial,

'Del' as Type,

Date

Where Len(Trim(Date)) > 0;

LOAD Serial,

If(Max(If(Type = 'Delrt', Date)) <= Max(If(Type = 'Del', Date)), Max(If(Type = 'Del', Date))) as Date

Resident Table

Group By Serial;

20 Replies

Re: Max data on the basis of field

Try This:

TMP:

load * Inline [

Qty, Type, Date, Serial_no, Dealer

1, Del, 13/05/2017, A1, D01

1, Delrt, 15/05/2017, A1, D01

1, Del, 12/08/2017, A2, D02

1, Del, 13/08/2017, A3, D03

1, Delrt, 14/05/2017, A3, D03

1, Del, 15/08/2017, A3, D04

1, Del, 16/08/2017, A4, D05

1, Delrt, 16/08/2017, A4, D05

1, Del, 17/08/2017, A4, D05

1, Del, 18/08/2017, A5, D06

1, Delrt, 19/08/2017, A5, D06

1, Del, 19/08/2017, A5, D06

];

Left Join

LOAD Type, Serial_no, Max(Date) as Date, '1' as This_is_the_Row Resident TMP

Where Type = 'Del'

Group by Type, Serial_no;

You have to use the column "This_is_the_Row "

sahadevpatil140
New Contributor III

Re: Max data on the basis of field

Hi Swati,

Use this Expression

=aggr(max(Date),Serial_no,Dealer)

hope this will work.

swatitomar
New Contributor III

Re: Max data on the basis of field

By mistaken written wrong date in front of type: Delrt from Serial A3

1Delrt14/05/2017A3D03

Correct 1 is

1Delrt14/08/2017A3D03
swatitomar
New Contributor III

Re: Max data on the basis of field

tired but Didn't get any relevant answer by this .

   

DateQtyDealerSerial_noThis_is_the_RowType
13/08/20171D03A3 Del
13/05/20171D01A1 Del
15/05/20171D01A1 Delrt
14/08/20171D03A3 Delrt
15/08/20171D04A3 Del
16/08/20171D05A4 Del
16/08/20171D05A4 Delrt
17/08/20171D05A4 Del
18/08/20171D06A5 Del
19/08/20171D06A5 Del
19/08/20171D06A5 Delrt
12/08/20171D02A21Del

Re: Max data on the basis of field

I have tried with your data and the result is not what you show;

Send me the exact set of data....

swatitomar
New Contributor III

Re: Max data on the basis of field

I load your data and share you the result.

I want Serial ,dealer wise chart in front end with Max Del Date data to show the data to del cycle to user.

DateSerialDealer
12/08/2017A2D02
15/08/2017A3D03
17/08/2017A4D05
19/08/2017A5D06
sahadevpatil140
New Contributor III

Re: Max data on the basis of field

I have used following expression

=aggr(max({<Type={'Del'}>}Date),Serial_no)

its work fine.Max date.JPG

swatitomar
New Contributor III

Re: Max data on the basis of field

QtyTypeDateSerialDealer
1Del13/05/2016A1D01
1Delrt15/05/2016A1D01
1Del12/08/2017A1D01
1Del13/08/2017A3D03
1Delrt14/05/2017A3D03
1Del15/08/2017A3D04
1Delrt15/08/2017A3D04
1Del15/05/2018A3D04
1Del16/08/2017A4D05
1Delrt16/08/2017A4D05
1Del17/08/2017A4D05
1Del18/08/2017A5D06
1Delrt19/08/2017A5D06
1Del19/08/2017A5D06

I checked but issue is in case of Serial No. A1, A3 .

I want when user select 2016 or 2016 with month 05 , he will not any data. he will get the data only when he select 2017 or 2017 with month 08 month.

Same with A3.

So I decided to work in backend n remove the entries in backend only.

Please check it

MVP
MVP

Re: Max data on the basis of field

May be this

Table:

LOAD * INLINE [

    Qty, Type, Date, Serial, Dealer

    1, Del, 13/05/2016, A1, D01

    1, Delrt, 15/05/2016, A1, D01

    1, Del, 12/08/2017, A1, D01

    1, Del, 13/08/2017, A3, D03

    1, Delrt, 14/05/2017, A3, D03

    1, Del, 15/08/2017, A3, D04

    1, Delrt, 15/08/2017, A3, D04

    1, Del, 15/05/2018, A3, D04

    1, Del, 16/08/2017, A4, D05

    1, Delrt, 16/08/2017, A4, D05

    1, Del, 17/08/2017, A4, D05

    1, Del, 18/08/2017, A5, D06

    1, Delrt, 19/08/2017, A5, D06

    1, Del, 19/08/2017, A5, D06

];


Right Join (Table)

LOAD Dealer,

Serial,

Type,

Max(Date) as Date

Resident Table

Where Type = 'Del'

Group By Dealer, Serial, Type;

Capture.PNG

Community Browser