Discussion board where members can get started with Qlik Sense.
I've a table setup like this :
And i'm wondering if there is a way to merge them to have a table like this:
And will i have to create a new table for this ? or is it possible to change the first one ?
Thanks for answer/tips
Go to Solution.
I see, so you have a key to match
then follow this logic:
Name as First
where Definition='First name';
Name as Last
where Definition='Last name';
First&' '&Last as Name
drop table table_tmp;
Try this inload script:
(so the ID 1 match ID 2):
ID-1 as ID,
Name as Name2
Name&' '&Name_2 as Name
drop table table1;
If you mistyped , and the data look like this:
1 First_name Andrew
1 Last_name Smith
Name as Firstname
Where Definition = 'First Name';
Name as Lastname
Where Definition = 'Last Name';
Firstname&' '&Lastname as Name
Hope this helps.
using above ()
Is the firstname and Lastname allways correctly sorted
1 firstname 1
2 lastname 1
3 firstname 2
4 lastname 2
No not always sadly i forgot to mention that!
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.
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
Where Definition='Last Name';
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
here is a example you are copy the coding in a New qvw
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;
here the expample as qvw
load * inline [
rowno registrarynumber type value
4 100 Firstname Max
5 100 Lastname Gro
] (delimiter is spaces)
load registrarynumber, value as First Resident input where type = 'Firstname';
join (output) load registrarynumber, value as Last Resident input where type = 'Lastname';