Merge fields and multiple crosstable

[ElviraD]

Hi team!

I have a problem with restructuring a table. Does someone know the way to go from this:

Person	Name1	Street1	HouseNr1	Name2	Street2	HouseNr2
1	Mary	Muster Str.	123
2				John	Sample Str.	456

to this:

Person	Name	Address
1	Mary	Muster Str. 123
2	John	Sample Str. 456

Thank you in advance! I would really appreciate any advices 🙂

[jochem_zw]

something like this:

Person:
Load
Person,
Name1 as Name,
Street1 as Street,
HouseNr1 as HouseNr
From Table
Where not is null(Name1);

Concatenate(Person)
Load
Person,
Name2 as Name,
Street2 as Street,
HouseNr2 as HouseNr
From Table
Where not is null(Name2);

[ElviraD]

Thank you Jochem for your quick response!

I had this idea in mind as well. But my raw data can be different in different cases. Do you know if there is a possibility to do it somehow in the loop? (e.g. starting from the 2nd field: take 1 field, merge 2 others and then take another field again) or this is not possible?

[jochem_zw]

you can create a nested if statement:

if(not is null(name1),name1,if(not is null(name2),name2,if(not is null(name3),name3))) as Name

etc.

[StarinieriG · Accepted solution]

Hi,

you could try with something like this

NoConcatenate
MAX:
LOAD
Max(Person) AS MAX_PERSON
Resident TEST;

LET MaxPerson = Peek('MAX_PERSON',0,'MAX');

For p = 1 to $(MaxPerson)

TABLE:
LOAD
Person,
Name$(p) AS Name,
Street$(p) AS Street,
HouseNr$(p) AS HouseNr
Resident TEST
Where Person='$(p)'
;

NEXT

DROP Tables
TEST,
MAX
;

[ElviraD]

Hi StarinieriG,

Thank you very much! It worked 🙂