topic Re: Help Sql select in QlikView

Help Sql select

Thu, 23 Aug 2012 21:11:39 GMT

Dear all,

I'm facing the follwing problem:

Table1

Order Nr Ord.Date CampaignCode

100 01-02-12 PROMO

101 15-04-12 PROMO

Table2

CampaignCode Order nr. other infor concerning Campaign

PROMO 100 other data

PROMO 101 ....

Table3

Campaigncode Customer Start / End Date

PROMO A 01-01-12 31-03-12

PROMO B 01-01-12 30-04-12

I need to get all the records where Order date is included into the Campaign start/end date, in this case the first and last line.

How can I solve it by an sql select ?

Help please

Thanks in advance for your help

Gr.

Stefan

Re: Help Sql select

Fri, 24 Aug 2012 05:41:41 GMT

Hi Stefan,

The easiest way is joining the three tables to one and the loading all dates with the where-condition Ord.Date >= Startdate AND Ord.Date <= Enddate.

Or you use the IntervalMatch.

Both you cann see in the attached file.

Regards Vicky

Re: Help Sql select

senpradip007 — Fri, 24 Aug 2012 07:19:06 GMT

Hi peronist,

you can use condition like Ord.Date >= Startdate and Ord.Date <= Enddate

Hope it helps you.

Regards

Pradip

Re: Help Sql select

Fri, 24 Aug 2012 08:29:51 GMT

Hi Vicky,

I'm working with the personal edition and I'm not able to open your file. Could you put your script here, please ?

Thanks very much !!

Gr.

Re: Help Sql select

Tue, 28 Aug 2012 05:21:46 GMT

Good Morning Stefan,

Here you are

Table1:

LOAD * INLINE [
    Campaign, Costumer, Startdate, Enddate
    PROMO, A, 01.01.2012, 31.03.2012
    PROMO, A, 01.03.2012, 31.03.2012
    PROMO, B, 01.01.2012, 30.04.2012
    PROMO, C, 01.06.2012, 31.08.2012
    PROMO, D, 01.09.2012, 31.10.2012
    PROMO, D, 01.03.2012, 30.06.2012
    PROMO, E, 01.05.2012, 31.07.2012
];

Table2:
LOAD * INLINE [
    Order Nr, Ord.Date, Campaign
    100, 01.02.2012, PROMO
    101, 15.04.2012, PROMO
    102, 02.03.2012, PROMO
    103, 06.06.2012, PROMO
    104, 05.09.2012, PROMO
    105, 07.10.2012, PROMO
    106, 05.04.2012, PROMO
];
left join IntervalMatch ([Ord.Date]) LOAD Startdate, Enddate resident Table1;

I hope this is want you want.

Regards

Vicky