Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
khan_fayez
New Contributor III

Dynamically Concat the fields alphabetically

Hi,

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

   

Field AField BField CField DConcatMeasure
alphaGammaBetaXetaalphaBetaGammaXeta1000
DABCABCD3000
Tom SayerDarren RoachJay KempnerDon BurtonDon BurtonDarren RoachJay KempnerTom Sayer4000

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.

12 Replies
MVP
MVP

Re: Dynamically Concat the fields alphabetically

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

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;

Capture.PNG

Employee
Employee

Re: Dynamically Concat the fields alphabetically

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
Honored Contributor

Re: Dynamically Concat the fields alphabetically

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
alphaGammaBetaXetaBetaGammaXetaalpha
DABCABCD
Tom SayerDarren RoachJay KempnerDon BurtonDarren RoachDon BurtonJay KempnerTom Sayer
khan_fayez
New Contributor III

Re: Dynamically Concat the fields alphabetically

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

alphaGammaBetaXeta

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

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.

khan_fayez
New Contributor III

Re: Dynamically Concat the fields alphabetically

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

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

Employee
Employee

Re: Dynamically Concat the fields alphabetically

alpha Beta Gamma Xeta is not in alphabetical order

lower "a' comes after upper "B"


Community Browser