Build straight customer hierachy in script

[alex59800]

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

[alex59800]

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

[#3]

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]

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

[alex59800]

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

[somenathroy]

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]

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

[#8]

Hello Alex,

Hope you're fine

Check the attached app based on the new rules.