topic Union tables. in QlikView

Union tables.

Anonymous — Mon, 16 Aug 2010 21:06:37 GMT

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?

Union tables.

Mon, 16 Aug 2010 22:36:35 GMT

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;

Union tables.

Anonymous — Tue, 17 Aug 2010 12:57:11 GMT

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.

Union tables.

Tue, 17 Aug 2010 15:39:58 GMT

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;