Script Customer Table Help

[Anonymous]

Hi! I need your HELP please!

I've a table in QLIK-VIEW with 2 columns. One is name of customer, two his group-leader (if exists).

The group-leader is in turn a customer present in the first column.

In this case exists 3 groups of customers:

• YELLOW group with customers C1, C3, C7, C5, C8, C9.
• BLUE group with customers C4, C6
• WHITE group with a customer C2 without group leader.

Now I create a Multiple Box filter named CUSTOMER_SELECTION.

If I select C1 I'd like to come out the customer table with C1, C3, C7, C5, C8, C9. And the same if I select C3, C5, C7, etc

If I select C4 I'd like to come out the customer table with C4. And the same if I select C6.

If I select C2 I'd like to come out C2 only.

Examples:

How can the script be created?

Thank you!!!!

Marco

[felipedl]

Hi Marco,

I added another column, called "Class" as to the group colors you mentioned (YELLOW,BLUE and WHITE)

Data:
load * Inline
[
Group,Leader,Class
C1,,YELLOW
C2,,WHITE
C3,C7,YELLOW
C4,C6,BLUE
C5,C8,YELLOW
C6,,BLUE
C7,C8,YELLOW
C8,C9,YELLOW
C9,C1,YELLOW
];

With this data, I created a filter with the following expression:

=aggr(only({<Class=P(Class),Group=P({<Group>}Group)>}Group),Group)

The aggr will return a table, with the possible values for Groups where the class (YELLOW,WHITE or BLUE) is equal to the selected.

So, if I select C1, which has the YELLOW Class, it will bring all group values of this Class, as seen bellow:

Attached the QVW I used.

Hope it helps,

Felipe.

[Anonymous]

I Felipe, thank's for your kindness but I can't added another column.

The customers are 6'000 and the "Class" must be found automatically.

If I select C1, the table have to display C1, C3, C5, C7, C8, C9 because these customer are related. I select a customer and the table have to display his group of customers.

[bc-thebruuu]

Ciao Marco

Can you share a test app, pls?

[Anonymous]

Ok, but that's contain the problem.

Example, if  I choose CUSTOMER_TEMP=C4, result is a table with C4, C6... it's ok. The same if I choose CUSTOMER_TEMP=C6. (C6 is leader of C4)

Also if I chose CUSTOMER_TEMP=C2, appear only C2 because this customer have no leader.

For "groups" of 1 or 2 customer it's ok.

But if I choose CUSTOMER_TEMP=C5 I'd like to find all the customer related, so:

C3 and his leader C7.

C7 and his leader C8, which in turn is a leader of C5

C8 and his leader C9

C9 and his leader C1...

Recap, a table with C1, C3, C5, C7, C8 and C9!

[bc-thebruuu · Accepted solution]

Sounds like you have to use hierarchy and hierarchy belong to functions

Please take a look at qlik help about those two functions.

Meanwhile I keep on working on it

[Anonymous]

Thank's Bruno! It's a step forward! 🙂

Now I'm here, look the attached app...

I need that if I select a single CUSTOMER, the table is filtered for the whole GROUP.

How can I do?

[bc-thebruuu]

Ciao Marco

First,

the hierarchy was not aligned with the description you provided:

Here's the good one

Hierarchy(NodeID, ParentID, NodeName, NodeParent, NodeName, NodePath, '/', NodeDepth) LOAD * inline

[

NodeID, ParentID, NodeName

C1,root,C1

C2,root,C2

C6,root,C6

C3,C7,C3 

C4,C6,C4 

C5,C8,C5    

C7,C8,C7 

C8,C9,C8 

C9,C1,C9

];

NodeName was containing a copy of ParentID in the version you provided

[Anonymous]

Ciao Bruno!

Grazie!...

Now how can I make sure that if I select a single CUSTOMER I get all the group in the right table?

Example... I select C3 in the left filter and obtain a list of C1,C3,C5,C7,C8, C9 on the right table.