Condition for Where Statement - Between Dates

[Anonymous]

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])

;

[robert_mika · Accepted solution]

Try:

Table 1

join

Table 2

then

Load *

Resident  Table1

where...

drop table table1;

Feeling Qlikngry?

How To /Missing Manual(25 articles)

[sunny_talwar]

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

[#4]

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;

[Anonymous]

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.

[Anonymous]

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

[robert_mika]

Look at my solution.

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

[Anonymous]

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