Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

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

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;


talk is cheap, supply exceeds demand
6 Replies
Not applicable

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

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
Not applicable

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

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
Not applicable

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

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;


talk is cheap, supply exceeds demand
Not applicable

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

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

Thanks

Not applicable

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

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;

Not applicable

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

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 ?