topic Re: Logic in QlikView

Logic

aveeeeeee7en — Fri, 04 Apr 2014 09:00:56 GMT

Hi Community

I have one issue:

Load * Inline [

Key,   Date1 ,        Date2
AAA,   21/3/2014 ,     4/4/2014
AAA,   22/3/2014 ,    10/4/2014
AAA,   24/3/2014 ,     8/4/2014

BBB, 20/3/2014 , 15/4/2014

BBB, 25/3/2014 , 17/4/2014

];

Desired Output Required:

Key Date1 Date2

AAA 24/3/2014 10/4/2014

BBB 25/3/2014 15/4/2014

Need Suggestions

Regards

Aviral Nag

Re: Logic

sivarajs — Fri, 04 Apr 2014 09:45:22 GMT

Check your date format it might be a string value. Convert to date format and apply your logic.

One way to convert string is Date(Date#(Date1,'dd/m/yyyy'),'DD/MM/YYYY')

Re: Logic

aveeeeeee7en — Fri, 04 Apr 2014 09:54:39 GMT

This is not Date Format Issue. This is related to the Logic.

Re: Logic

crusader_ — Fri, 04 Apr 2014 10:07:30 GMT

Hi,

I can suggest you to separate your table on 2 tables, because of extreme values are in different rows.

I would made first table with Key and Max(Date1), second table with Key and Max(Date2) and afterwards join tables or use ApplyMap()

Hope it helps.

Andrei

Re: Re: Logic

Fri, 04 Apr 2014 10:14:27 GMT

Use this script

Load * Inline [

Key, Date1, Date2
AAA, 21/3/2014, 4/4/2014
AAA, 22/3/2014, 10/4/2014
AAA, 24/3/2014, 8/4/2014
BBB, 20/3/2014, 15/4/2014
BBB, 25/3/2014, 17/4/2014

];

Left Join
B:
Load
Key,
Date(Max(Date#(Date1,'DD/MM/YYYY')),'DD/MM/YYYY') as MaxDate1,
Date(Max(Date#(Date2,'DD/MM/YYYY'),2),'DD/MM/YYYY') as MaxDate2
Resident A
Group By Key;

Re: Logic

nizamsha — Fri, 04 Apr 2014 10:20:37 GMT

Try this one it will work

TableA:

Load * Inline [

Key, Date1, Date2

AAA, 21/3/2014, 4/4/2014

AAA, 22/3/2014, 10/4/2014

AAA, 24/3/2014, 8/4/2014

BBB, 20/3/2014, 15/4/2014

BBB, 25/3/2014, 17/4/2014

];

//NoConcatenate

//LOAD max(Date1) as DAte3,Key Resident TableA Group by Key;

load MinString(Date1) as Date1,Key

Resident TableA

group by Key,1;

drop table TableA;

Re: Logic

tresB — Fri, 04 Apr 2014 10:27:08 GMT

Use a straight table;

dimension: Key

expression1: Max(Date1)

expression2: Max(Date2)

Re: Logic

aveeeeeee7en — Fri, 04 Apr 2014 10:29:21 GMT

Thanks for your reply.

I need Max(date,2) As per the Date1. Youe Logic doesn't give me desired result. Kindly see the output required in the top post.

Re: Logic

aveeeeeee7en — Fri, 04 Apr 2014 10:31:25 GMT

Hi Tresesco Sir

I actually want this:

Load * Inline [

Key,   Date1 ,        Date2
AAA,   21/3/2014 ,     4/4/2014
AAA,   22/3/2014 ,    10/4/2014
AAA,   24/3/2014 ,     8/4/2014

BBB, 20/3/2014 , 15/4/2014

BBB, 25/3/2014 , 17/4/2014

];

Desired Output Required:

Key Date1 Date2

AAA 24/3/2014 10/4/2014

BBB 25/3/2014 15/4/2014

Max(Date2) wont give me this

Re: Logic

crusader_ — Fri, 04 Apr 2014 10:37:48 GMT

What is the logic in your result?

I mean you need to find max(Date1) for every Key

and how(?) or what(?) do you want to find with Date2 ???

Re: Re: Logic

nizamsha — Fri, 04 Apr 2014 10:40:12 GMT

PFA the output u asked is attached

Re: Logic

aveeeeeee7en — Fri, 04 Apr 2014 10:45:48 GMT

To pick Second Last Date2 on the basis of Max Date1

The Date2 coming against the Max Date1, I need its above date.

Re: Logic

Fri, 04 Apr 2014 10:49:26 GMT

hey check this..

Re: Logic

tresB — Fri, 04 Apr 2014 10:59:09 GMT

Now I got your logic. Try this expression for Date2:

=FirstSortedValue(Date2,-Date1, 2)

Dimension: Key

Re: Re: Logic

Fri, 04 Apr 2014 11:08:53 GMT

see the modified one

Load * Inline [

Key, Date1, Date2
AAA, 21/3/2014, 4/4/2014
AAA, 22/3/2014, 10/4/2014
AAA, 24/3/2014, 8/4/2014
BBB, 20/3/2014, 15/4/2014
BBB, 25/3/2014, 17/4/2014

];

B:
Load
Key,
Date(Max(Date#(Date1,'DD/MM/YYYY')),'DD/MM/YYYY') as MaxDate1
Resident A
Group By Key;

C:
Load *
where MaxDate2 <> Null();

NoConcatenate
Load
Key,
If(Key='AAA',Date(Max(Date#(Date2,'DD/MM/YYYY')),'DD/MM/YYYY')) as MaxDate2
Resident A
Group By Key;

😧
Load *
where MaxDate2 <> Null();

NoConcatenate
Load
Key,
If(Key='BBB',Date(Min(Date#(Date2,'DD/MM/YYYY')),'DD/MM/YYYY')) as MaxDate2
Resident A
Group By Key;