topic Re: Table load using another table in QlikView

Table load using another table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Table load using another table

eduardo_dimperio — Fri, 27 Apr 2018 17:02:55 GMT

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

Re: Table load using another table

Anonymous — Fri, 27 Apr 2018 19:01:38 GMT

I need all combinations like recursive self join sql

Re: Table load using another table

eduardo_dimperio — Fri, 27 Apr 2018 20:42:58 GMT

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

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;

Re: Table load using another table

MarcoWedel — Fri, 27 Apr 2018 23:12:15 GMT

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

Re: Table load using another table

Anonymous — Sat, 28 Apr 2018 18:01:34 GMT

Thank you Marco, It is working as expected

Re: Table load using another table

eduardo_dimperio — Thu, 03 May 2018 20:36:18 GMT

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