topic Create Field between two tables in QlikView

Create Field between two tables

Wed, 02 Dec 2009 17:36:49 GMT

Hello

I have 2 tables and I would like create a field which use one field in the first table and one field in the second table.

Table 1 :

LOAD COACTREF, //Key with TYPEACTIV
Year(DTJRNCAL) as [Année],
Month(DTJRNCAL) as [Mois],
Week(DTJRNCAL) as [Semaine],
Day(DTJRNCAL) as [Jour],
DUREEREA;
SQL SELECT *
FROM ACTIVITY;

Table 2 :

LOAD COACTREF, //Key with ACTEMPJRN
INCALCUL;
SQL SELECT *
FROM TYPEACTIV;

Fields I would like create are :

- If (Table2.INCALCUL = 'O',Table1.DUREEREA,0)

- If (Table2.INCALCUL = 'N',Table1.DUREEREA,0)

Thanks to your help

Create Field between two tables

Anonymous — Wed, 02 Dec 2009 17:40:14 GMT

The tables are connected with the key COACTREF, so I think the if statement should just work fine as it is.

I assume you use the if statements somewhere in the frontend of the QlikView application (in a pivot table?) ... give it a try I should say.

Create Field between two tables

Wed, 02 Dec 2009 17:47:06 GMT

Thanks to you answer but I would like create fields in the script. I don't want to create fields everytime.

Create Field between two tables

Anonymous — Wed, 02 Dec 2009 17:53:38 GMT

You could consider something like this:



Temp:
LOAD COACTREF,
Year (DTJRNCAL) as [Annee],
Month(DTJRNCAL) as [Mois],
Week(DTJRNCAL) as [Semaine],
Day(DTJRNCAL) as [Jour],
DUREEREA;
SQL SELECT * FROM ACTIVITY;

LEFT JOIN
LOAD COACTREF,
INCALCUL;
SQL SELECT *
FROM TYPEACTIV;

Table:
LOAD COACTREF,
Annee,
Mois,
Semaine,
Jour,
DUREEREA,
INCALCUL,
if(INCALCUL = 'O', DUREEREA,0) as INCALCUL_O,
if(INCALCUL = 'N' DUREEREA,0) as INCALCUL_N
RESIDENT Temp;

Don't see why you should do it this way ... since the tables are connected with the COACTREF
the INCALCUL is already connected to the DUREEREA of the same COACTREF of the other table (oke now it is in one table) ...

If you mean otherwise could you be more specific?

Create Field between two tables

Wed, 02 Dec 2009 19:12:17 GMT

Thanks for you answer.

Create Field between two tables

Wed, 02 Dec 2009 19:40:44 GMT

QlikView create two tables : temp and temp-1, is it normal ?

Create Field between two tables

Wed, 02 Dec 2009 19:44:13 GMT

Sorry, QlikView create 2 tables : Table and Temp.

But I don't want see the table Temp, how can I make ?

Create Field between two tables

Anonymous — Wed, 02 Dec 2009 19:47:42 GMT

Sorry ... forgot the drop table


DROP TABLE TEMP;

This way the TEMP table is removed and only the Table is in the application.

Create Field between two tables

Wed, 02 Dec 2009 19:52:38 GMT

Thanks