Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vincent_bellang
New Contributor III

Merge fields

Greetings,

I've a table setup like this :

IDDefinitionName
1First NameAlexander
2Last NameWise
3First NameChris
4Last NameDamico
5First NameFrederick
6Last NameLeviathan

And i'm wondering if there is a way to merge them to have a table like this:

IDName
1Alexander Wise
3Chris Damico
5Frederick Leviathan

And will i have to create a new table for this ? or is it possible to change the first one ?

Thanks for answer/tips

Tags (1)
1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Merge fields

I see, so you have a key to match

then follow this logic:

table_tmp:

load

     registrary_number,

     Name as First

from [table]

where Definition='First name';

left join(table_tmp)

Load

     registrary_number,

     Name as Last

from

where Definition='Last name';

Table_final:

Load

     registrary_number,

    First&' '&Last as Name

Resident table_tmp;

drop table table_tmp;

G.

12 Replies
undergrinder
Valued Contributor II

Re: Merge fields

Hi vincent,

Try this inload script:

(so the ID 1 match ID 2):

table1:

Load

     ID,

     Name

From/Resident Table

Where mod(id,2)=1;

left join(table1)

Load

     ID-1 as ID,

     Name as Name2

From/Resident Table

Where mod(id,2)=0;

table_final:

Noconcatenate

Load

     ID,

     Name&' '&Name_2 as Name

resident table1;

drop table table1;

If you mistyped , and the data look like this:

1 First_name Andrew

1 Last_name Smith

then

table1:

Load

     ID,

     Name as Firstname

From/Resident Table

Where Definition = 'First Name';

Left join(table1)

Load

     ID,

     Name as Lastname

From/Resident Table

Where Definition = 'Last Name';

Load

     ID,

     Firstname&' '&Lastname as Name

resident table1;

drop table table1;

Hope this helps.

G.

gerry_hdm
Contributor II

Re: Merge fields

hello

using  above ()

Is the firstname and Lastname allways correctly sorted

1 firstname 1

2 lastname 1

3 firstname 2

4 lastname 2

etc......

??

vincent_bellang
New Contributor III

Re: Merge fields

No not always sadly i forgot to mention that!

undergrinder
Valued Contributor II

Re: Merge fields

but the consecutive ID-s, match right?

1 with 2, 3 with 4?

Because after an ordering Gerold's answer can be good, after filter out the 2-3, 4-5 pairs.

G.

florianblum
New Contributor

Re: Merge fields

Either the IDs must match, IDs must be assigned in descending order. For this a solution has already been posted or the sorting has to fit

for correct sorting the script would look like that:

LOAD previous(ID) as ID

           previous(Name)&' '&Name as field

Resident tablename

Where Definition='Last Name';

vincent_bellang
New Contributor III

Re: Merge fields

No they are not sometime it can be like this

1 Firstname Alex

2 Firstname Jean

3 Lastname Leviathan

Some of them didn't put their last name for example

I'm sorting them by a registrary number like this

1 156   Firstname Alex

2 1155 Firstname Jean

3 1155 Lastname Leviathan

So the matching pair would always have the same Registrary number

Maybe something like this would be easier to get ?

1 156    Alex

2 1155 Jean Leviathan

gerry_hdm
Contributor II

Re: Merge fields

hello Vicent

here is a example you are copy  the coding in a New qvw

greetings gerrold

// Example

ReadTable:
load * inline [
ID, Definiton,Name
1, FirstName, Alexander
2, LastName, Wise
3, FirstName, Chris
4, LastName, Damico
5, FirstName, Frederick
6, LastName, Leviathan
]
;

EndTable:
load *
where not FirstLastName = 'Del'
;
load 
if (Definiton  = 'LastName' , previous(Name)&','&Name,'Del') as FirstLastName ,
previous(ID) as ID
resident ReadTable;


drop Table ReadTable;

gerry_hdm
Contributor II

Re: Merge fields

here the expample as qvw

:-)

Gerry

MVP
MVP

Re: Merge fields

Try this

1.png

input:

load * inline [

rowno registrarynumber type value

1 156   Firstname Alex

2 1155 Firstname Jean

3 1155 Lastname Leviathan

4 100 Firstname Max

5 100 Lastname Gro

] (delimiter is spaces)

;

output:

load registrarynumber, value as First Resident input where type = 'Firstname';

join (output) load registrarynumber, value as Last Resident input where type = 'Lastname';

Community Browser