topic Re: Condition for Where Statement - Between Dates in QlikView

Condition for Where Statement - Between Dates

Anonymous — Tue, 26 Jul 2016 10:53:21 GMT

Hi Guys

Need a script for the following:

Need to pull all data in Table 2 where Date A (in Table 1) is between Start and End Date in Table 2.

Joining on Key

Any assistance will be appreciated:

Table1:
LOAD [Employee],
     [Key],
     [Record Number],
     [Date A],
[Status]

FROM
[..\.qvd]
(qvd)

;

Table2:
LOAD [Key],
[Structure Entity],
[Start Date],
[End date],
Region,
Division,
Plant
FROM
[..\QVData\.qvd]
(qvd)

Where ([Date A] >= [Start Date] and [Date A] <= [End date])

;

Re: Condition for Where Statement - Between Dates

robert_mika — Tue, 26 Jul 2016 12:15:06 GMT

Try:

Table 1

join

Table 2

then

Load *

Resident Table1

where...

drop table table1;

Feeling Qlikngry?

How To /Missing Manual(25 articles)

Re: Condition for Where Statement - Between Dates

sunny_talwar — Tue, 26 Jul 2016 12:16:00 GMT

How about this:

Table1:
LOAD [Employee],
    [Key],
    [Record Number],
    [Date A],
    [Status]

FROM [..\.qvd] (qvd);

Table2:

Left Keep (Table1)

LOAD *,

Date([Start Date] + IterNo() - 1) as [Date A]

While [Start Date] + IterNo() - 1 <= [End Date];

LOAD [Key],
     [Structure Entity],
     [Start Date],
     [End date],
     Region,
     Division,
    Plant
FROM [..\QVData\.qvd] (qvd);

Re: Condition for Where Statement - Between Dates

Tue, 26 Jul 2016 12:19:02 GMT

try this...adapt if i am not undestand...

Table2:
LOAD [Key],
[Structure Entity],
[Start Date],
[End date],
Region,
Division,
Plant
FROM
[..\QVData\.qvd]
(qvd);

Filter:

load max([End date]) as max_date, min([End date]) as min_date resident Table2;

let max_dt=fieldvalue(max_date,1);

let min_dt=fieldvalue(min_date ,1);

drop table Filter;

inner join (Table2)

//Table1:
LOAD [Employee],
     [Key],
     [Record Number],
     [Date A],
[Status]

FROM
[..\.qvd]
(qvd) where [Date A] >= min_dt and [Date A] <=max_dt;

Re: Condition for Where Statement - Between Dates

Anonymous — Tue, 26 Jul 2016 12:49:13 GMT

Hi Sunny

The statement looks right, but now Table2 had 330 000 rows.

Will it load 330 000 x 330 000?

Is there not a more elegant solution?

I only want one record for Table2 where DateA from Table 1 is between Start Date and End date in Table2.

All other records in table2 can be disregarded.

Re: Condition for Where Statement - Between Dates

Anonymous — Tue, 26 Jul 2016 12:51:03 GMT

Won't work, need a each row in table2 where DateA from Table 1 is between Start Date and End date in Table2.

Can't use a max date. Needs to look at Start and End Date for each record in Table2 and compare with Date A in Table1

Re: Condition for Where Statement - Between Dates

robert_mika — Tue, 26 Jul 2016 18:13:46 GMT

Look at my solution.

You are joining two tables and then taking what you need.

Re: Condition for Where Statement - Between Dates

Anonymous — Tue, 26 Jul 2016 19:57:34 GMT

Hi Robert

I actually did look at this solution this afternoon and it's brilliantly simple!

I love solutions that are simple and achieve the goal in as little transformation as possible.

Thank you