topic Build straight customer hierachy in script in QlikView

Build straight customer hierachy in script

alex59800 — Fri, 20 Jul 2012 09:52:47 GMT

Hello all

I need help to build straight customer hierarchy troght script.

I have an example attached.

Thanks a lot for your help.

Best regards

Alex

Re: Build straight customer hierachy in script

alex59800 — Fri, 20 Jul 2012 09:56:08 GMT

for this hierarchy in addition to the file attached, i need to fill in the empty last level with 99999

HIERARCHY 1	HIERARCHY 2	HIERARCHY 3	HIERARCHY 4
Customer A	Customer B	Customer C	Customer D
Customer E	Customer F	Customer H	999999

Thanks a lot

Re: Build straight customer hierachy in script

Mon, 23 Jul 2012 21:47:10 GMT

Hi Alex,

Check the file attached.

As for the dates, I assumed that you want to ignore all records where End Date has already passed.

Hope this will suit your needs.

Re: Build straight customer hierachy in script

alex59800 — Tue, 24 Jul 2012 08:00:16 GMT

Hello JP

Work well thanks a lot. I copied your scipt with the original table to see result

Parent	Child	StartDate	EndDate
Customer A	Customer B	01/01/2010	31/12/2019
Customer B	Customer C	01/01/2010	31/12/2019
Customer C	Customer D	01/01/2010	31/12/2019
Customer E	Customer F	01/01/2010	31/12/2019
Customer F	Customer G	01/01/2010	30/06/2011
Customer F	Customer H	01/07/2010	31/12/2019

Just a detail

The result give me that final table :

For the 2 line it should not be loaded as a valid hierarchy as the endate is 30/06/2011.

Could you telle me what's happening ?

Thank a lot

Alex

Re: Build straight customer hierachy in script

alex59800 — Tue, 24 Jul 2012 09:24:34 GMT

Table final not attached up here

Hierarchy1	Hierarchy2	Hierarchy3	Hierarchy4
Customer A	Customer B	Customer C	Customer D
Customer E	Customer F	Customer G	999999
Customer E	Customer F	Customer H	999999

Re: Build straight customer hierachy in script

somenathroy — Wed, 25 Jul 2012 14:55:36 GMT

Hi,

You may try the below code:

DataIn:

LOAD * Inline

[HIERARCHY (Father), HIERARCHY (Son), StartDate, EndDate

Customer A, Customer B, 01-01-10, 31-12-19

Customer B, Customer C, 01-01-10, 31-12-19

Customer C, Customer D, 01-01-10, 31-12-19

Customer E, Customer F, 01-01-10, 31-12-19

Customer F, Customer G, 01-01-10, 30-06-11

Customer F, Customer H, 01-07-10, 31-12-19];

Data1:

LOAD [HIERARCHY (Father)], [HIERARCHY (Son)] Resident DataIn;

Left Join

LOAD [HIERARCHY (Son)] as [HIERARCHY (Father)], 1 as OnlyFather Resident DataIn;

Data2:

NoConcatenate

LOAD

[HIERARCHY (Father)] as HIERARCHY1,

[HIERARCHY (Son)] as HIERARCHY2

Resident Data1 Where IsNull(OnlyFather);

Left Join

LOAD

[HIERARCHY (Father)] as HIERARCHY2,

[HIERARCHY (Son)] as HIERARCHY3

Resident DataIn;

DROP Table Data1;

Data3:

NoConcatenate

LOAD

HIERARCHY1,

HIERARCHY2,

HIERARCHY3

Resident Data2 ;

Left Join

LOAD

[HIERARCHY (Father)] as HIERARCHY3,

[HIERARCHY (Son)] as HIERARCHY4

Resident DataIn;

DROP Table Data2;

DROP Table DataIn;

DataOut:

NoConcatenate

LOAD

HIERARCHY1,

If(IsNull(HIERARCHY2), 999999, HIERARCHY2) as HIERARCHY2,

If(IsNull(HIERARCHY3), 999999, HIERARCHY3) as HIERARCHY3,

If(IsNull(HIERARCHY4), 999999, HIERARCHY4) as HIERARCHY4

Resident Data3;

DROP Table Data3;

Regards,

Som

Re: Build straight customer hierachy in script

alex59800 — Thu, 02 Aug 2012 14:03:36 GMT

ok i changed the rules a little bit because the final result is not ok.

New rules are mentionned with example in the file attached

Thanks a lot for your help

Alex

Re: Build straight customer hierachy in script

Fri, 03 Aug 2012 00:35:12 GMT

Hello Alex,

Hope you're fine

Check the attached app based on the new rules.