topic Re: Use between operatore in qlikview back end in QlikView

Re: Use between operatore in qlikview back end

gireesh1216 — Wed, 25 Nov 2020 16:16:04 GMT

This data coming from A Database:
Table1:
Start_Date,End_Date
01-01-2016,10-01-2016
15-01-2016,25-01-2016

This data coming from B Database:

table2:
CREATE_DT
01-01-2016
10-01-2016
15-01-2016
25-01-2016
17-01-2016 - this data is between 15-01-2016 to 25-01-2016
11-01-2016 -These dates not there in range of start date and end date of table 1
14-01-2016 --These dates not there in range of start date and end date of table 1

Looking OUTPUT like below:

CREATE_DT
01-01-2016
10-01-2016
15-01-2016
25-01-2016
17-01-2016

query : Where CREATE_DT >= '$(vStart_Date)' AND CREATE_DT <= '$(vEnd_Date)'; will get all data from 01-01-2016 to 25-01-2016.

I don't want 11-01-2016 and 14-01-2016 in my output.

so the above query not working for my requirmnet.

Please help me

Re: Use between operatore in qlikview back end

PunamWagh — Tue, 30 Jan 2018 10:57:50 GMT

Try this,

Table1:

Load Start_Date,

End_date

From databaseA;

noconcatenate

temp:

max(Start_Date) as Start_Date,

max(End_Date) as End_Date

resident Table1;

LET vStart_Date = Peek('Start_Date', 0, 'temp');

LET vEnd_Date = Peek('End_Date', 0, 'temp');

drop table temp:

Table2:

load CREATE_DT

from databaseB

Where CREATE_DT >= '$(vStart_Date)' AND CREATE_DT <= '$(vEnd_Date)' ;

Re: Use between operatore in qlikview back end

gireesh1216 — Tue, 30 Jan 2018 11:03:39 GMT

The above query also gives all data. Can u please check my sample data.

Re: Use between operatore in qlikview back end

PunamWagh — Tue, 30 Jan 2018 12:05:40 GMT

Try This:

Table1:

LOAD * INLINE [

Start_Date,End_Date

01-01-2016,10-01-2016

15-01-2016,25-01-2016

];

temp:

LOAD

Date(Start_Date +iterno()-1) as Date

resident Table1 while Start_Date+IterNo()-1 <=End_Date;

Table2:

load *

where Exists(Date,CREATE_DT);

LOAD * INLINE [

CREATE_DT

01-01-2016

10-01-2016

15-01-2016

25-01-2016

17-01-2016

11-01-2016

14-01-2016

];

drop table temp;

Re: Use between operatore in qlikview back end

gireesh1216 — Tue, 30 Jan 2018 12:56:06 GMT

Not Working

Re: Use between operatore in qlikview back end

PunamWagh — Wed, 31 Jan 2018 06:26:35 GMT

Same is working for me..

PFA

Re: Use between operatore in qlikview back end

shiveshsingh — Wed, 31 Jan 2018 07:25:50 GMT

Try this

B:load * inline [
CREATE_DT
01-01-2016
10-01-2016
15-01-2016
25-01-2016
17-01-2016];

A: load * inline [
Start_Date,End_Date
01-01-2016,10-01-2016
15-01-2016,25-01-2016];

IntervalMatch(CREATE_DT)
load Start_Date,End_Date
Resident A;

Re: Use between operatore in qlikview back end

gireesh1216 — Wed, 31 Jan 2018 07:32:18 GMT

Yes...Its working fine...

Thank u so much