Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aveeeeeee7en
Valued Contributor 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

Tags (1)
14 Replies
sivarajs
Valued Contributor II

Re: Logic

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
Valued Contributor III

Re: Logic

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

crusader_
Valued Contributor

Re: Logic

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

Re: Re: Logic

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
Valued Contributor

Re: Logic

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;

MVP
MVP

Re: Logic

Use a straight table;

dimension: Key

expression1: Max(Date1)

expression2: Max(Date2)

aveeeeeee7en
Valued Contributor III

Re: Logic

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
Valued Contributor III

Re: Logic

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_
Valued Contributor

Re: Logic

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

Community Browser