topic Re: Compare dates from different records in QlikView

Compare dates from different records

arpitkharkia — Thu, 24 Aug 2017 14:06:04 GMT

Consider the following table:

LOAD * INLINE [

GR, RT, QID, CD, Date

1, 8, 11, 6/1/2017, 8/1/2017

1, 10, 22, 9/1/2017, 8/1/2017

1, 15, 33, 15/1/2017, 8/1/2017

2, 9, 66, 4/1/2017,10/1/2017

2, 12, 44, 8/1/2017, 10/1/2017

2, 15, 55, 11/1/2017, 10/1/2017

];

If Date is greater than CD of the row and less than CD of the next row than that row has to be considered. For the above table i need the following output:

GR	RT	QID	CD	Date
1	8	11	6/1/2017	8/1/2017
2	12	44	8/1/2017	10/1/2017

I hope its possible. Help appreciated!

Re: Compare dates from different records

sunny_talwar — Thu, 24 Aug 2017 14:10:13 GMT

You need this in the script or front end?

Re: Compare dates from different records

arpitkharkia — Thu, 24 Aug 2017 14:19:25 GMT

I need this in the script itself. Need to join certain tables after that.

Re: Compare dates from different records

Anonymous — Thu, 24 Aug 2017 14:51:37 GMT

You may try this script:

tmp_table1:

LOAD * INLINE [

GR, RT, QID, CD, Date

1, 8, 11, 6/1/2017, 8/1/2017

1, 10, 22, 9/1/2017, 8/1/2017

1, 15, 33, 15/1/2017, 8/1/2017

2, 9, 66, 4/1/2017,10/1/2017

2, 12, 44, 8/1/2017, 10/1/2017

2, 15, 55, 11/1/2017, 10/1/2017

];

tmp_table2:

load

GR, RT, QID, CD, Date, Peek(CD,RowNo(),'tmp_table1') as prevCD

Resident tmp_table1;

NoConcatenate

table1:

load *

Resident tmp_table2

where Date>CD and Date<prevCD

;

DROP Tables tmp_table1, tmp_table2;

Re: Compare dates from different records

arpitkharkia — Fri, 25 Aug 2017 06:13:13 GMT

Thanks for the reply! But the solution is not what i want.

Re: Compare dates from different records

Anonymous — Fri, 25 Aug 2017 06:43:57 GMT

You are welcome, but please explain why this is not what you want, maybe I didn't understand your requirement correctly.

Re: Compare dates from different records

antoniotiman — Fri, 25 Aug 2017 07:09:31 GMT

Hi Arpit,

maybe this

Temp:
LOAD *,RowNo() as Rowno INLINE [
GR, RT, QID, CD, Date
1, 8, 11, 6/1/2017, 8/1/2017
1, 10, 22, 9/1/2017, 8/1/2017
1, 15, 33, 15/1/2017, 8/1/2017
2, 9, 66, 4/1/2017,10/1/2017
2, 12, 44, 8/1/2017, 10/1/2017
2, 15, 55, 11/1/2017, 10/1/2017
];
Temp1:
NoConcatenate LOAD *
Resident Temp
Where Date > CD and Date < Previous(CD)
Order By Rowno desc;
Drop Table Temp;

Regards,

Antonio

Re: Compare dates from different records

tresB — Fri, 25 Aug 2017 07:24:09 GMT

Try like:

Re: Compare dates from different records

arpitkharkia — Fri, 25 Aug 2017 07:25:20 GMT

Thanks Antonio for the reply. But i dont know why im getting only one row as output

Any reason for this?

im running the same script.

Re: Compare dates from different records

tresB — Fri, 25 Aug 2017 07:25:59 GMT

I should have refreshed the page before posting.

Re: Compare dates from different records

Anonymous — Fri, 25 Aug 2017 07:29:50 GMT

This works as well:

tmp_table1:

LOAD * INLINE [

GR, RT, QID, CD, Date

1, 8, 11, 6/1/2017, 8/1/2017

1, 10, 22, 9/1/2017, 8/1/2017

1, 15, 33, 15/1/2017, 8/1/2017

2, 9, 66, 4/1/2017, 10/1/2017

2, 12, 44, 8/1/2017, 10/1/2017

2, 15, 55, 11/1/2017, 10/1/2017

];

NoConcatenate

table1:

load *

Resident tmp_table1

where Date>CD and Date<Peek(CD,RecNo(),'tmp_table1');

DROP Table tmp_table1;

Re: Compare dates from different records

antoniotiman — Fri, 25 Aug 2017 14:07:47 GMT

Maybe Your variable Date Format setting in Script.

See Attachment.

Re: Compare dates from different records

arpitkharkia — Mon, 28 Aug 2017 09:17:58 GMT

Yeah thats what. Thank you so much for the help!