topic Re: Script Customer Table Help in QlikView

Script Customer Table Help

Anonymous — Tue, 15 May 2018 14:10:20 GMT

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

Re: Script Customer Table Help

felipedl — Wed, 16 May 2018 11:48:59 GMT

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.

Re: Script Customer Table Help

Anonymous — Wed, 16 May 2018 12:54:04 GMT

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.

Re: Script Customer Table Help

bc-thebruuu — Wed, 16 May 2018 13:12:35 GMT

Ciao Marco

Can you share a test app, pls?

Re: Script Customer Table Help

Anonymous — Wed, 16 May 2018 13:55:56 GMT

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!

Re: Script Customer Table Help

bc-thebruuu — Wed, 16 May 2018 14:07:54 GMT

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

Re: Script Customer Table Help

Anonymous — Thu, 17 May 2018 07:26:10 GMT

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?

Re: Script Customer Table Help

bc-thebruuu — Thu, 17 May 2018 12:20:10 GMT

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

Re: Script Customer Table Help

Anonymous — Tue, 22 May 2018 10:21:20 GMT

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.