Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alex59800
Contributor
Contributor

Build straight customer hierachy in script

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

7 Replies
alex59800
Contributor
Contributor
Author

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

HIERARCHY 1HIERARCHY 2HIERARCHY 3HIERARCHY 4
Customer ACustomer BCustomer CCustomer D
Customer ECustomer FCustomer H 999999


Thanks a lot

Not applicable

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.

alex59800
Contributor
Contributor
Author

Hello JP

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

ParentChildStartDateEndDate
Customer ACustomer B01/01/201031/12/2019
Customer BCustomer C01/01/201031/12/2019
Customer CCustomer D01/01/201031/12/2019
Customer ECustomer F01/01/201031/12/2019
Customer FCustomer G01/01/201030/06/2011
Customer FCustomer H01/07/201031/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

alex59800
Contributor
Contributor
Author

Table final not attached up here

Hierarchy1Hierarchy2Hierarchy3Hierarchy4
Customer ACustomer BCustomer CCustomer D
Customer ECustomer FCustomer G999999
Customer ECustomer FCustomer H999999
somenathroy
Creator III
Creator III

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

alex59800
Contributor
Contributor
Author

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

Not applicable

Hello Alex,

Hope you're fine

Check the attached app based on the new rules.