Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Follow case – AsOfMonth ? Match ? Other ?

I’m absolutely stuck here. I have 3 tables:

RS (contains cases that have moved from product 0 to 1):

RS_CaseID, RS_date,    Case_ID

1111,          15-03-2012,  2282

Cases (contains all cases whether moved from 0 to 1, from 1 to 2 or have always and only been in 0, 1 or 2):

Case_ID, Reg_date,    Product

1111,      15-03-2012,  0

2282,      16-06-2012,  1 (this one has been moved from 0 and has a new date)

3363,      30-09-2012,  2 (this one has been moved from 1 and has a new date)

4444,      30-09-2012,  1 (this one has always only existed in 1)

Ebev (contains cases that have moved from product 1 to 2):

EbevCaseID, Ebev_date, Case_ID

2282,          16-06-2012, 3363

I need the following in the load script:

If a case is moved from product 0 to 1, the case in 1 should keep the date from product 0.

If a case is moved from product 0 to 1 and 1 to 2, the case in 1 and 2 should keep the date from product 0.

If a case is moved from product 1 to 2 but never have been in product 0, the case in 2 should keep the date from product 1.

So in short: keep the very first date always even if the case_id and date has changed in Cases table. Can anyone help ?

NB: cases can only move from 0 to 1 and 1 to 2. No other combinations are possible.

1 Solution

Accepted Solutions
fosuzuki
Valued Contributor II

Re: Follow case – AsOfMonth ? Match ? Other ?

Kinda ugly, but I think it works:

Cases:

LOAD * inline [

Case_ID, Reg_date,    Product

1111,      15-03-2012,  0

2282,      16-06-2012,  1

3363,      30-09-2012,  2

4444,      30-09-2012,  1

];

Left Join

LOAD * inline [

From0To1, RS_date,    Case_ID

1111,          15-03-2012,  2282

];

Left Join

LOAD * inline [

From1To2, Ebev_date, Case_ID

2282,          16-06-2012, 3363

];

Left Join

LOAD * inline [

From0To1_, RS_date_,    From1To2

1111,          15-03-2012,  2282

];

Left Join

LOAD Case_ID,

           alt(RS_date_, RS_date, Ebev_date, Reg_date) AS Date

Resident Cases;

2 Replies
fosuzuki
Valued Contributor II

Re: Follow case – AsOfMonth ? Match ? Other ?

Kinda ugly, but I think it works:

Cases:

LOAD * inline [

Case_ID, Reg_date,    Product

1111,      15-03-2012,  0

2282,      16-06-2012,  1

3363,      30-09-2012,  2

4444,      30-09-2012,  1

];

Left Join

LOAD * inline [

From0To1, RS_date,    Case_ID

1111,          15-03-2012,  2282

];

Left Join

LOAD * inline [

From1To2, Ebev_date, Case_ID

2282,          16-06-2012, 3363

];

Left Join

LOAD * inline [

From0To1_, RS_date_,    From1To2

1111,          15-03-2012,  2282

];

Left Join

LOAD Case_ID,

           alt(RS_date_, RS_date, Ebev_date, Reg_date) AS Date

Resident Cases;

Not applicable

Re: Follow case – AsOfMonth ? Match ? Other ?

You are an absolute genious ! Never mind the ugliness, it works like a charm (at least on test data). Thank you ever so much

Community Browser