topic Re: Dynamically Concat the fields alphabetically in QlikView

Dynamically Concat the fields alphabetically

Anonymous — Wed, 10 Aug 2016 17:21:05 GMT

Hi,

I need to concatenate different fields in my load script alphabetically leaving other fields unchanged.

Field A	Field B	Field C	Field D	Concat	Measure
alpha	Gamma	Beta	Xeta	alphaBetaGammaXeta	1000
D	A	B	C	ABCD	3000
Tom Sayer	Darren Roach	Jay Kempner	Don Burton	Don BurtonDarren RoachJay KempnerTom Sayer	4000

The desired output will be Concat field in the above example. It is basically shifting the fields left to right or vice versa alphabetically without effecting other fields.

Re: Dynamically Concat the fields alphabetically

swuehl — Wed, 10 Aug 2016 18:13:47 GMT

Maybe like

INPUT:

LOAD [Field A],

[Field B],

[Field C],

[Field D],

Concat,

Measure

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

CROSS:

CROSSTABLE (Field, Value,2)

LOAD Recno() as ID,

Measure,

[Field A],

[Field B],

[Field C],

[Field D]

RESIDENT INPUT;

LOAD ID, Measure, Concat(Value) as ConcatCross

RESIDENT CROSS

GROUP BY ID, Measure;

DROP TABLES CROSS, INPUT;

Re: Dynamically Concat the fields alphabetically

sunny_talwar — Wed, 10 Aug 2016 18:16:12 GMT

May be like this:

Table:

CrossTable (FieldName, Values, 2)

LOAD 1 as Temp,

Measure,

[Field A],

[Field B],

[Field C],

[Field D]

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

TempTable:

LOAD AutoNumber(RecNo(), FieldName) as Key,

Measure,

Temp,

FieldName,

Values

Resident Table;

FinalTable:

LOAD RowNo() as Key,

Measure,

[Field A],

[Field B],

[Field C],

[Field D]

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

Left Join (FinalTable)

LOAD Key,

Concat(Values, ', ') as Concat

Resident TempTable

Group By Key;

DROP Tables Table, TempTable;

Re: Dynamically Concat the fields alphabetically

Clever_Anjos — Wed, 10 Aug 2016 18:22:14 GMT

Maybe this:

LOAD [Field A],

[Field B],

[Field C],

[Field D],

[Field A] as [Field],

Measure

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

LOAD [Field A],

[Field B],

[Field C],

[Field D],

[Field B] as [Field],

Measure

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

LOAD [Field A],

[Field B],

[Field C],

[Field D],

[Field C] as [Field],

Measure

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

LOAD [Field A],

[Field B],

[Field C],

[Field D],

[Field D] as [Field],

Measure

FROM

[https://community.qlik.com/thread/228663]

(html, codepage is 1252, embedded labels, table is @1);

noconcatenate LOAD [Field A],

[Field B],

[Field C],

[Field D],

Measure,

Concat([Field]) as Concat

Resident T

Group by

[Field A],

[Field B],

[Field C],

[Field D],

Measure;

Drop Table T;

Re: Dynamically Concat the fields alphabetically

effinty2112 — Wed, 10 Aug 2016 21:42:39 GMT

Hi Fayez,

Data:

Load

RecNo() as RowNum,

LOAD * INLINE [

Field A, Field B, Field C, Field D

alpha, Gamma, Beta, Xeta

D, A, B, C, ABCD, 3000

Tom Sayer, Darren Roach, Jay Kempner, Don Burton

];

CrossData:

CrossTable(Field,Value) LOAD * Resident Data;

Concat:

LOAD

RowNum,

Concat(Value) as Concat

Resident CrossData Group by RowNum;

Left Join (Data)

LOAD * Resident Concat;

Drop tables CrossData, Concat;

DROP Field RowNum;

Gives:

Field A	Field B	Field C	Field D	Concat
alpha	Gamma	Beta	Xeta	BetaGammaXetaalpha
D	A	B	C	ABCD
Tom Sayer	Darren Roach	Jay Kempner	Don Burton	Darren RoachDon BurtonJay KempnerTom Sayer

Re: Dynamically Concat the fields alphabetically

Anonymous — Thu, 11 Aug 2016 09:30:35 GMT

The concat field above is the result I want. Basically I just need to merge the fields Field A, Field B, Field C, Field D into one single field. But the catch is these should be merged in sorted order (each row). For example I have values like

alpha

Gamma

Beta

Xeta

in four different fields. So the merged field should be : alphaBetaGammaXeta.

Similarly

Tom Sayer

,Darren Roach

,Jay Kempner,

Don Burton

Don BurtonDarren RoachJay Kempner Tom Sayer.

Please note that Don Burton comes ahead of Darren Roach because Burton comes first and than Roach if you sort in A-Z order

The explanations or solutions you have provided does the concatenation but not in alphabetical order.

Re: Dynamically Concat the fields alphabetically

sunny_talwar — Thu, 11 Aug 2016 09:45:48 GMT

Please note that Don Burton comes ahead of Darren Roach because Burton comes first and than Roach if you sort in A-Z order

This is true if you sort by last name. Are you looking to sort names and you want to sort them by last name? I mean what about fields which have three names John J. Smith? still sort by the last name? There needs to be some consistency within the data to ensure that one sort order can be used all across.

Re: Dynamically Concat the fields alphabetically

Anonymous — Thu, 11 Aug 2016 10:25:54 GMT

I need to sort the entire string. So while comparing a string suppose I have something like John J. Smith and John A. Smith and John J. Curl. The correct order will be John A. Smith John J. Curl John J. Smith. So the sorting has to be done on complete string which can have n no. of values

Re: Dynamically Concat the fields alphabetically

sunny_talwar — Thu, 11 Aug 2016 10:55:28 GMT

I guess I understand that. But what would be the sort order for something like this:

John J. Smith

John C. Smith

John J. Carlos

John C. Carlos

James J. Smith

James C. Smith

James J. Carlos

James C. Carlos

Re: Dynamically Concat the fields alphabetically

Clever_Anjos — Thu, 11 Aug 2016 11:06:19 GMT

alpha Beta Gamma Xeta is not in alphabetical order

lower "a' comes after upper "B"

Re: Dynamically Concat the fields alphabetically

Anonymous — Thu, 11 Aug 2016 12:26:09 GMT

This is a good question, but I think I will not have a situation wherein every field will start with same alphabet. Be it first name middle name or last name.

Re: Dynamically Concat the fields alphabetically

sunny_talwar — Thu, 11 Aug 2016 14:29:22 GMT

Will not happen, but I am just trying to understand the logic for sorting if something like this happens

Re: Dynamically Concat the fields alphabetically

Anonymous — Thu, 11 Aug 2016 14:36:17 GMT

I don't know about that situation. May be sorting on the first alphabets is all what is needed