Join several columns from a table with a single column in an other table

[#1]

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

[#2]

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

[jagan]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[#5]

For the moment, I use Gysbert solution. Martin's solution doesn't work for my problem

Thanks

[#6]

hi

Gysbert Wassenaar

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;

[#7]

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 ?