Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I start to work with QV 2 weeks ago, and I have a problem.
I want to join several columns from a table with a single column in an other table.
Exemple :
Table1:
LOAD
idApp,
AppName;
SELECT * FROM Table1;
Table2:
LOAD
idTable2,
idApp1,
idApp2,
idApp3;
SELECT * FROM Table2;
------------------------------------------
I want to join all idApp in Table2 with idApp in Table1.
I can't modify the data base (It is a great pity !), and I can't modify the LOAD statement of Table1 because I use a loop to load a lot of table in the script, it's a specificity of this table.
I tried to use the same alias on columns when I load the Table2 (idApp1 AS idApp, idApp2 AS idApp, idApp3 AS idApp) but I think it is not possible.
My only solution is to create 3 times the Table2 with different name, but I don't like it.
Can someone help me ?
Thanks
If all you want is to match the AppNames with the AppIDs you could try this:
AppMap:
mapping SELECT idApp,AppName FROM Table1;
Table2:
LOAD
idTable2, idApp1, idApp2, idApp3,
applymap('AppMap',idApp1) as AppName1,
applymap('AppMap',idApp2) as AppName2,
applymap('AppMap',idApp3) as AppName3;
SELECT idTable2, idApp1, idApp2, idApp3 FROM Table2;
Table2:
LOAD
idTable2,
idApp;
SELECT idTable2, idApp1 AS idApp FROM Table2 UNION
SELECT idTable2, idApp2 AS idApp FROM Table2 UNION
SELECT idTable2, idApp3 AS idApp FROM Table2;
Cheers
Hi,
Try like this
Table1:
SELECT * FROM Table1;
Table2Temp:
SELECT * FROM Table2;
Table2:
LOAD
idTable2,
idApp1 AS idApp
RESIDENT Table2Temp;
CONCATENATE(Table2)
LOAD
idTable2,
idApp2 AS idApp
RESIDENT Table2Temp;
CONCATENATE(Table2)
LOAD
idTable2,
idApp3 AS idApp
RESIDENT Table2Temp;
DROP TABLE Table2Temp;
Hope this helps you.
Regards,
Jagan.
If all you want is to match the AppNames with the AppIDs you could try this:
AppMap:
mapping SELECT idApp,AppName FROM Table1;
Table2:
LOAD
idTable2, idApp1, idApp2, idApp3,
applymap('AppMap',idApp1) as AppName1,
applymap('AppMap',idApp2) as AppName2,
applymap('AppMap',idApp3) as AppName3;
SELECT idTable2, idApp1, idApp2, idApp3 FROM Table2;
For the moment, I use Gysbert solution. Martin's solution doesn't work for my problem
Thanks
hi
solution is working correct.
you can also try this
Table1:
LOAD
idApp,
AppName;
SELECT * FROM Table1;
Table2:
LOAD
idTable2,
idApp1 as idApp,
idApp2,
idApp3;
SELECT * FROM Table2;
join1:
LOAD
idTable2,
idApp2 as idApp
resident Table2;
join2:
LOAD
idTable2,
idApp3 as idApp
resident Table2;
I try :
Apporteur:
LOAD id_apporteur,
apporteurName
societeuser,
hash128(id_apporteur, societeuser) AS KEYFICTAPPORT;
SQL SELECT * FROM apporteur;
Polices:
LOAD
id_police,
societeuser,
apporteur,
apporteur2,
apporteur3,
hash128(apporteur, societeuser) AS KEYFICTAPPORT;
SQL SELECT * FROM polices;
Join1:
LOAD *,
hash128(apporteur2, societeuser) AS KEYFICTAPPORT
RESIDENT Polices;
Join2:
LOAD *,
hash128(apporteur3, societeuser) AS KEYFICTAPPORT
RESIDENT Polices;
----------------------------------------------------------------------------------------------
It doesn't work
what is the problem ?