Whether Permutations and Combinations are possible..

[pra_kale]

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.

[swuehl]

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.

[swuehl]

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;

[pra_kale]

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.

[swuehl · Accepted solution]

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;

[pra_kale]

Too Good...Swuehl..

That's exactly what I want...

Thanks once Again Swuehl.

[#7]

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; 

But in this case, Jack|Jeff|Joan, Jeff|Jack|Joan, Joan|Jeff|Jack,.... are regarded as 6 different values, but since order doesn't matter here, I want to regard these as only one case. Can you please help?

BR, Peter

[swuehl]

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;

[#9]

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