Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aveeeeeee7en
Specialist III
Specialist III

Logic

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

14 Replies
sivarajs
Specialist II
Specialist II

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')

aveeeeeee7en
Specialist III
Specialist III
Author

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

crusader_
Partner - Specialist
Partner - Specialist

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

Not applicable

Use this script

 

A:

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;

nizamsha
Specialist II
Specialist II

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;

tresesco
MVP
MVP

Use a straight table;

dimension: Key

expression1: Max(Date1)

expression2: Max(Date2)

aveeeeeee7en
Specialist III
Specialist III
Author

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.

aveeeeeee7en
Specialist III
Specialist III
Author

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

crusader_
Partner - Specialist
Partner - Specialist

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 ???