topic Re: Whether Permutations and Combinations are possible.. in QlikView

Whether Permutations and Combinations are possible..

pra_kale — Sat, 25 Jun 2016 13:53:53 GMT

Hi,

I have attached one application in which i have given few types which is a combination of two alphabets like AB, AC, AD and so on.

whether it is possible where i can generate combinations which are not yet used from alphabets A...to.. Z and should be in capital letters.

Can you please assist by way i can get the desired out put.

Thanks in advance.

Re: Whether Permutations and Combinations are possible..

swuehl — Sat, 25 Jun 2016 14:00:36 GMT

Maybe like this

LOAD CHR(ORD('A')+recno()-1)&CHR(ORD('A')+Iterno()-1) as NewType

AutoGenerate 26

While Iterno() <=26;

An alternate approach would be to use cartesian product, a JOIN of two fields that contain all values, but are named differently. Then you would need to use a subsequent resident load to concatenate the two fields.

Re: Whether Permutations and Combinations are possible..

swuehl — Sat, 25 Jun 2016 14:04:41 GMT

If you need to check for already loaded field values:

Permutations:

LOAD Type

FROM

[Permutations.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD Type

WHERE NOT EXISTS(Type);

LOAD CHR(ORD('A')+recno()-1)&CHR(ORD('A')+Iterno()-1) as Type

AutoGenerate 26

While Iterno() <=26;

Re: Whether Permutations and Combinations are possible..

pra_kale — Sat, 25 Jun 2016 14:52:20 GMT

Hi,

Thanks Swuehl for your Help...

I have tried with the codes which you have given it is generating the combinations as per the requirement but only thing is the combinations which are already used should be get excluded, whether it possible.

Re: Whether Permutations and Combinations are possible..

swuehl — Sat, 25 Jun 2016 15:03:48 GMT

Shouldn't the second code sample do exactely this?

Or maybe you want to drop the combinations loaded from the input file:

Exclude:

LOAD Type

FROM

[Permutations.xlsx]

(ooxml, embedded labels, table is Sheet1);

Permutations:

NOConcatenate

LOAD Type

WHERE NOT EXISTS(Type);

LOAD CHR(ORD('A')+recno()-1)&CHR(ORD('A')+Iterno()-1) as Type

AutoGenerate 26

While Iterno() <=26;

DROP TABLE Exclude;

Re: Whether Permutations and Combinations are possible..

pra_kale — Sat, 25 Jun 2016 15:12:43 GMT

Too Good...Swuehl..

That's exactly what I want...

Thanks once Again Swuehl.

Re: Whether Permutations and Combinations are possible..

Thu, 25 May 2017 07:48:03 GMT

Hi Swuehl,

I'm trying below code to make the field vendor_combine

I need it as a combination of "vendor" and does not regard the order of vendors.

T1:
LOAD * INLINE [
Category, vendor, price
A , Jack , 1000
A , Jeff , 200
A , Peter , 202
A , Kate , 2333
A , Joan , 2331
A , John , 3000
B , Jack , 2000
B , Jeff , 3000
B , Alex , 3900
];

Join
LOAD Category,
vendor as vendor1
Resident T1
;

Join
LOAD Category,
vendor as vendor2
Resident T1
;

T2:
NoConcatenate LOAD *,
vendor&'|'&vendor1&'|'&vendor2 as vendor_combine
Resident T1
Where vendor<>vendor1
and vendor1<>vendor2
and vendor<>vendor2;

BR, Peter

Re: Whether Permutations and Combinations are possible..

swuehl — Thu, 25 May 2017 19:01:17 GMT

Not quite sure what you finally want to achieve, but may a Concat() aggregation is the better approach here:

T1:

LOAD * INLINE [

Category, vendor, price

A , Jack , 1000

A , Jeff , 200

A , Peter , 202

A , Kate , 2333

A , Joan , 2331

A , John , 3000

B , Jack , 2000

B , Jeff , 3000

B , Alex , 3900

];

LOAD Category,

Concat(DISTINCT vendor,'|') as vendor_combine

RESIDENT T1

GROUP BY Category;

Re: Whether Permutations and Combinations are possible..

Fri, 26 May 2017 07:50:09 GMT

Hi,

Sorry for not making this clear.

Actually I want to have all possible 3-vendor-combinations on Category level, the wanted result is:

Category combo_3_vendor indicator

A Jack|Jeff|Joan 10|30|8

A Jack|Jeff|John 12|41|9

A Jack|Jeff|Kate 8|21|7

A Jack|Jeff|Peter 6|14|6

A Jack|Joan|John 30|62|12

A Jack|Kate|Joan 20|42|10

A Jack|Kate|John 24|53|11

A Jack|Peter|Joan 15|35|9

A Jack|Peter|John 18|46|10

A Jack|Peter|Kate 12|26|8

A Jeff|Joan|John 60|65|13

A Jeff|Kate|Joan 40|45|11

A Jeff|Kate|John 48|56|12

A Jeff|Peter|Joan 30|38|10

A Jeff|Peter|John 36|49|11

A Jeff|Peter|Kate 24|29|9

A Kate|Joan|John 120|77|15

A Peter|Joan|John 90|70|14

A Peter|Kate|Joan 60|50|12

A Peter|Kate|John 72|61|13

B Jack|Jeff|Alex 6|14|6

Please have a look at this: Calculate min value accross a random range

I think I have achieved it but with too much code... will be interesting to have a leaner approach.

BR, Peter