Dynamically Concat the fields alphabetically

[Anonymous]

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.

[swuehl]

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;

[sunny_talwar]

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;

[Clever_Anjos]

Maybe this:

T:

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;  

[effinty2112]

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

[Anonymous]

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.

[sunny_talwar]

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.

[Anonymous]

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

[sunny_talwar]

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

[Clever_Anjos]

alpha Beta Gamma Xeta is not in alphabetical order

lower "a' comes after upper "B"