Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Do you need to do all combinations or just that ones?
I need all combinations like recursive self join sql
//***********************************************************
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;
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
Thank you Marco, It is working as expected
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