topic Re: Autonumber function in QlikView

Autonumber function

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

Guys,

I have 15 columns in a table, but I want to generate keys using autonumber function for all the combinations of that 15 columns. Is it possible to do by script wise?

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 11:38:54 GMT

Should be possible...

AutoNumber(Field1&Field2&Field3&.....&Field15) as Key

Re: Autonumber function

Kushal_Chawda — Wed, 26 Jul 2017 11:52:00 GMT

Autonumber (key1&'|'&Key2&'|'&.........)

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 11:53:29 GMT

No, I want all the combinations. I mean

for example, for three columns itself I want these much of combinations,

AutoNumber(Field1&Field2&Field3) as Key

AutoNumber(Field1&Field3&Field2) as Key1

AutoNumber(Field3&Field1&Field2) as Key2

AutoNumber(Field2&Field1&Field3) as Key3

So, it goes on. Likewise, is it possible to generate key for all the combinations of 15 columns.

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 11:55:12 GMT

I think so... the only change I would do is this

AutoNumber(Field1&Field2&Field3, 'Key') as Key

AutoNumber(Field1&Field3&Field2, 'Key1') as Key1

AutoNumber(Field3&Field1&Field2, 'Key2') as Key2

AutoNumber(Field2&Field1&Field3, 'Key3') as Key3

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 11:56:43 GMT

Thank you. But do I have to write all the combinations of 15 columns or can I use loop function for that?

Re: Autonumber function

Kushal_Chawda — Wed, 26 Jul 2017 11:56:52 GMT

that menas 15 * 15 = 225 combinations?

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 11:57:49 GMT

yes exactly..

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 11:58:58 GMT

I guess you might be able to loop... let me see if there is an option

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 12:08:07 GMT

This is for 3 fields, but can be expanded to 15 fields

Table:

LOAD *,

RowNo() as UK;

LOAD * Inline [

ABC, DEF, GHI

1, 2, 3

92, 32, 54

3, 4, 2

];

FieldNames:

LOAD * Inline [

FieldName

ABC

DEF

GHI

];

FinalTable:

LOAD UK

Resident Table;

FOR i = 1 to FieldValueCount('FieldName')

LET vField1 = FieldValue('FieldName', $(i));

LET vField2 = FieldValue('FieldName', If($(i)+1 > 3, FieldValueCount('FieldName') - $(i) + 1, $(i)+1));

LET vField3 = FieldValue('FieldName', If($(i)+2 > 3, FieldValueCount('FieldName') - $(i) + 2, $(i)+2));

Left Join (FinalTable)

LOAD UK,

AutoNumber([$(vField1)]&'|'&[$(vField2)]&'|'&[$(vField3)], 'Key'&$(i)) as [Key$(i)]

Resident Table;

NEXT i

Re: Autonumber function

effinty2112 — Wed, 26 Jul 2017 12:16:59 GMT

Hi Siva,

It would be quite a piece of work to produce a key for every possible combination from 15

No of fields chosen No of ways to combine

1 15

2 105

3 455

4 1365

5 3003

6 5005

7 6435

8 6435

9 5005

10 3003

11 1365

12 455

13 105

14 15

15 1

32767

Good luck!

Andrew

PS I should clarify. We're not talking about 32767 values for a key. We're talking 32767 different keys.

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 12:24:33 GMT

Fixed some errors and added the 4th field

Table:

LOAD *,

RowNo() as UK;

LOAD * Inline [

ABC, DEF, GHI, KLM

1, 2, 3, 4

92, 32, 54, 23

3, 4, 2, 2

];

FieldNames:

LOAD * Inline [

FieldName

ABC

DEF

GHI

KLM

];

FinalTable:

LOAD UK

Resident Table;

FOR i = 1 to FieldValueCount('FieldName')

LET vField1 = FieldValue('FieldName', $(i));

LET vField2 = FieldValue('FieldName', If($(i)+1 > 4, 1, $(i)+1));

LET vField3 = FieldValue('FieldName', If($(i)+2 > 4, $(i) - 2, $(i)+2));

LET vField4 = FieldValue('FieldName', If($(i)+3 > 4, $(i) - 1, $(i)+3));

TRACE $(vField1);

TRACE $(vField2);

TRACE $(vField3);

TRACE $(vField4);

Left Join (FinalTable)

LOAD UK,

AutoNumber([$(vField1)]&'|'&[$(vField2)]&'|'&[$(vField3)]&'|'&[$(vField4)], 'Key'&$(i)) as [Key$(i)]

Resident Table;

NEXT i

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 12:57:00 GMT

Thanks Sunny, But I have FieldName with 15 properties. I don't know where to use my properties and fieldname in your code. Could you please guide me?

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 12:57:43 GMT

Share few rows of raw data

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 13:01:33 GMT

Here is the sample data.

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 13:03:07 GMT

Which 15 fields are to be autonumbered here?

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 13:06:20 GMT

Except the Field Name column, all the other columns.

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 13:15:58 GMT

See if this helps

Re: Autonumber function

sunny_talwar — Wed, 26 Jul 2017 13:30:42 GMT

I guess these are only 15 keys and you might be looking for 225 keys.... not sure if I know of a better way to do this...

Re: Autonumber function

Anonymous — Wed, 26 Jul 2017 13:40:59 GMT

Thank you so much Sunny. I'll check it.