Table load using another table

[Anonymous]

Package	ID	Value
Misusing DD	1	1
Missing Bag	2	2
Missing Box	3	4

The above table I have , I need to create new table below:

Package	Value
Misusing DD	1
Missing Bag	2
Misusing DD, Missing Bag	3
Misusing DD, Missing Box	5
Missing Bag, Missing Bag	6
Misusing DD, Missing Bag, Missing Box	7

How to get the table like this? in QlikView Script

[eduardo_dimperio]

Do you need  to do all combinations or just that ones?

[Anonymous]

I need all combinations like recursive self  join sql

[eduardo_dimperio]

//***********************************************************

BASE:

load * inline [

PACKAGE,VALUE

Misusing DD,1

Missing Bag,2

Missing Box,4

];

//***********************************************************

NoConcatenate

AUX_TB1:

LOAD

PACKAGE AS PACKAGE1,

VALUE   AS VALUE1

RESIDENT BASE;

Inner Join(AUX_TB1)

LOAD

PACKAGE AS PACKAGE2,

VALUE   AS VALUE2

RESIDENT BASE;

NoConcatenate

AUX_TB2:

LOAD

*

RESIDENT AUX_TB1;

Inner Join(AUX_TB2)

LOAD

PACKAGE AS PACKAGE3,

VALUE   AS VALUE3

RESIDENT BASE;

//*********************************************

Concatenate(BASE)

LOAD

PACKAGE1 &','& PACKAGE2  AS PACKAGE,

VALUE1 +VALUE2 AS VALUE

RESIDENT AUX_TB1;

Concatenate(BASE)

LOAD

PACKAGE1 &','& PACKAGE2 &','& PACKAGE3  AS PACKAGE,

VALUE1 +VALUE2 +VALUE3 AS VALUE

RESIDENT AUX_TB2;

//*********************************************

DROP TABLE AUX_TB1;

DROP TABLE AUX_TB2;

EXIT SCRIPT;

[MarcoWedel · Accepted solution]

Hi,

one solution could be also:

table1:
LOAD * FROM [https://community.qlik.com/thread/299774] (html, codepage is 1252, embedded labels, table is @1);

mapPackage:
Mapping LOAD Value, Package Resident table1;

table2:
LOAD Concat(Package,', ',OrdNo) as Package, Value
Group By Value;
LOAD RecNo() as Value,
    IterNo() as OrdNo,
    ApplyMap('mapPackage',RecNo() bitand (1<<(IterNo()-1)),Null()) as Package
AutoGenerate 1<<FieldValueCount('Package')-1
While IterNo()<=FieldValueCount('Package');

DROP Table table1;

hope this helps

regards

Marco

[Anonymous]

Thank you Marco, It is working as expected

[eduardo_dimperio]

Hi Marco,

I really tried, but dont get how this works and why you choose this way. Can you explain please?

I follow your code and you(i think):

Deslocate  the InterNo()-1 in 1 bit (why?)

After, you use a comparisson between bits using bitand (why???)

and then you aply the mapping, but mapping dont need to be use in a comon field, you use in RecNo() bitand (1<<(IterNo()-1)) (why??!!??)

I'm really confuse about it