Union tables.

[Anonymous]

Hello,

I am not able to present these two tables,

Motorista_1	Motorista_2	Motorista_3		Valor_1	Valor_2	Valor_3
Jose	Jose	Jose		10	30	50
Joao	Joao	Joao		20	40	60
	Marcos				10

as follows:

Motorista	Valor_1	Valor_2	Valor_3
Jose	10	30	50
Joao	20	40	60
Marcos		10

Could someone help?

[#2]

That does not look like a fun data set, very un-Kimball. Depending on how much more complicated the real data is than the sample, something like this may work:

NamesTemp:
LOAD * INLINE [
Motorista_1,Motorista_2,Motorista_3
Jose,Jose,Jose
Joao,Joao,Joao
,Marcos,
];
ValuesTemp:
LOAD * INLINE [
Valor_1,Valor_2,Valor_3
10,30,50
20,40,60
,10,
];
FullTable:
LOAD RowNo() As FakeKey,
 If(Len(Motorista_1)>0, Motorista_1,
 If(Len(Motorista_2)>0, Motorista_2, Motorista_3)) As Motorista
RESIDENT NamesTemp;
JOIN LOAD RowNo() As FakeKey, *
RESIDENT ValuesTemp;
DROP TABLE NamesTemp;
DROP TABLE ValuesTemp;
DROP FIELD FakeKey;

[Anonymous]

Thanks for the reply, but failed. If you add two different names in the first two fields of the table NamesTemp he considers only the first field the other name of the second field does not return.

[#4]

Your sample didn't include any records like that. You have different names in one record in that table? If so, you'll need to load each column separately. You may be able to use a cross table, but creating three tables would also work.

Here's a load that should work:

NamesTemp:
LOAD * INLINE [
Motorista_1,Motorista_2,Motorista_3
Jose,NewGuy,Jose
Joao,Joao,Joao
,Marcos,
];
ValuesTemp:
LOAD * INLINE [
Valor_1,Valor_2,Valor_3
10,30,50
20,40,60
,10,
];
Comb1:
LOAD RowNo() As FakeKey, Motorista_1 As Motorista
RESIDENT NamesTemp
WHERE Len(Motorista_1) > 0;
JOIN LOAD RowNo() As FakeKey, Valor_1
RESIDENT ValuesTemp
WHERE Valor_1 > 0;
Comb2:
LOAD RowNo() As FakeKey, Motorista_2 As Motorista
RESIDENT NamesTemp
WHERE Len(Motorista_2) > 0;
JOIN LOAD RowNo() As FakeKey, Valor_2
RESIDENT ValuesTemp
WHERE Valor_2 > 0;
Comb3:
LOAD RowNo() As FakeKey, Motorista_3 As Motorista
RESIDENT NamesTemp
WHERE Len(Motorista_3) > 0;
JOIN LOAD RowNo() As FakeKey, Valor_3
RESIDENT ValuesTemp
WHERE Valor_3 > 0;
DROP TABLE NamesTemp;
DROP TABLE ValuesTemp;
DROP FIELD FakeKey;