Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
gireesh1216
Contributor II

Re: Use between operatore in qlikview back end

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

7 Replies
punam_wagh
New Contributor III

Re: Use between operatore in qlikview back end

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

gireesh1216
Contributor II

Re: Use between operatore in qlikview back end

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

punam_wagh
New Contributor III

Re: Use between operatore in qlikview back end

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;

gireesh1216
Contributor II

Re: Use between operatore in qlikview back end

Not Working

punam_wagh
New Contributor III

Re: Use between operatore in qlikview back end

Same is working for me..

PFA

shiveshsingh
Honored Contributor

Re: Use between operatore in qlikview back end

Hi

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;

gireesh1216
Contributor II

Re: Use between operatore in qlikview back end

Yes...Its working fine...

Thank u so much