Expanding data set

[Koen_D]

Hi qlik experts,

I have a table that looks like the following:

Input:

LOAD * INLINE [

ProcesCode, Person, Family

A, 1, 1

A, 2, 1

B, 2, 1

C, 3, 1

D, 4, 2

D, 5, 2

E, 6, 2

];

For some data modelling problem I need the output to be the following:

// Expected uitput:

Output:

LOAD * INLINE [

ProcesCode, Person, Family

A, 1, 1

A, 2, 1

A, 3, 1

B, 1, 1

B, 2, 1

B, 3, 1

C, 1, 1

C, 2, 1

C, 3, 1

D, 4, 2

D, 5, 2

D, 6, 2

E, 4, 2

E, 5, 2

E, 6, 2

];

Basically, a person only belongs to 1 family. A Person can have multiple ProcesCodes, and a ProcesCode can have multiple people. I want the code to "expand" (not sure about the right terminology) the table, so that every combination of ProcesCode and Person within a Family occurs in the output table.

The code should add new rows of ProcesCode / Person combinations that are not yet present within the Family, but do exist in some other combination of ProcesCode / Person within that family.

Not sure if my question is clear, but any tips on how to achieve this?

[martinpohl]

so, load your first table (Input).

Then

Output:

load distinct ProcesCode resident Input;

join load distinct Person resident Input;

join load distinct Family resident Input;

drop table Input;

Regards

[igoralcantara · Accepted solution]

I am not sure if I understood your question. For example: in the input table, for ProcessCode A, why do you have Person 3?

Regardless, I would try to load these in separate tables and do an outer join or a simple join with no qualifier.

[Koen_D]

In the Input table there is ProcesCode C, Person 3, Family 1. Therefore, the expected output table should add rows for each other Person in Family 1 with ProcesCode C. Because ProcesCode A and B are also part of persons from Family 1, we also need to add 2 rows with Person 3, ProcesCode A and Person 3, ProcesCode B. 

I've been looking at outer join and I'm sure there is a solution somewhere, haven't been able to quite find it.

[Koen_D]

Actually, after some meddling I've found it. Thanks! The solution was as follows:

Output:

NOCONCATENATE LOAD DISTINCT Family RESIDENT Input;

OUTER JOIN (Output)

LOAD Family, Person RESIDENT Input;

OUTER JOIN (Output)

LOAD Family, ProcesCode RESIDENT Input;