topic Re: JOIN in temporary table in QlikView

JOIN in temporary table

Anonymous — Fri, 15 Apr 2016 09:28:23 GMT

Hello,

i have a problem with my script.

I create the table "LAST_TIME" that contains 3 keys (IDDEVICE, IDPARK and IDFIELD) and extract the max DATETIME from the table GENERAL_INPUTS_DATA

LAST_TIME:

LOAD

IDDEVICE, IDPARK, IDFIELD,

MAX([DATETIME]) AS CURR_TIME

RESIDENT GENERAL_INPUTS_DATA

GROUP BY IDDEVICE, IDPARK, IDFIELD;

Example of result

IDPARK	IDDEVICE	IDFIELD	CUR_TIME
1	a	1	15/04/2016 12:00
1	b	1	15/04/2016 11:00
2	c	1	13/04/2016 06:00
2	d	1	15/04/2016 12:00

The table GENERAL_INPUT_DATA contains also data about ENERGY YELDED and i would obtain the ENERGY producted today.

LOAD Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

RESIDENT GENERAL_INPUTS_DATA

WHERE Year(DATETIME) = Year(Today(1))

AND Month(DATETIME) = Month(Today(1))

AND Day(DATETIME) = Day(Today(1))

GROUP BY IDDEVICE, IDPARK, IDFIELD;

Example of result

IDPARK	IDDEVICE	IDFIELD	ENERGY YELDED
1	a	1	50,00
1	b	1	20,00
2	c	1	0,00
2	d	1	13,00

How can i join the 2 table to obtain the next result ?

IDPARK	IDDEVICE	IDFIELD	CUR_TIME	ENERGY YELDED
1	a	1	15/04/2016 12:00	50,00
1	b	1	15/04/2016 11:00	20,00
2	c	1	13/04/2016 06:00	0,00
2	d	1	15/04/2016 12:00	13,00

In MySql i would use a query like this but with "RESIDENT" i can't use ALIAS (in this example, calling GENERAL_INPUTS_DATA G1 or G2 )

select g1.IDDEVICE, g1.IDPARK,

MAX(g1.`DATETIME`) AS CURR_TIME,

(

select Max(g2.`ENERGY YELDED`) - Min(g2.`ENERGY YELDED`) as CURR_ENERGY_TODAY

from `GENERAL INPUTS DATA` g2

WHERE

Year(g2.DATETIME) = Year(now(1)) AND Month(g2.DATETIME) = Month(now(1)) AND Day(g2.DATETIME) = Day(now(1))

and g2.IDPARK = g1.IDPARK and g1.IDDEVICE = g2.IDDEIVCE

GROUP BY g2.IDDEVICE, g2.IDPARK

)

from `GENERAL INPUTS DATA` g1

GROUP BY g1.IDDEVICE, g1.IDPARK;

Re: JOIN in temporary table

swuehl — Fri, 15 Apr 2016 09:34:14 GMT

Have you tried just joining your tables?

LAST_TIME:

LOAD

IDDEVICE, IDPARK, IDFIELD,

MAX([DATETIME]) AS CURR_TIME

RESIDENT GENERAL_INPUTS_DATA

GROUP BY IDDEVICE, IDPARK, IDFIELD;

JOIN (LAST_TIME)

LOAD Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

,IDDEVICE, IDPARK, IDFIELD

RESIDENT GENERAL_INPUTS_DATA

WHERE Year(DATETIME) = Year(Today(1))

AND Month(DATETIME) = Month(Today(1))

AND Day(DATETIME) = Day(Today(1))

GROUP BY IDDEVICE, IDPARK, IDFIELD;

Re: JOIN in temporary table

Kushal_Chawda — Fri, 15 Apr 2016 09:42:41 GMT

try this

LAST_TIME:

LOAD

IDDEVICE, IDPARK, IDFIELD,

MAX([DATETIME]) AS CURR_TIME

RESIDENT GENERAL_INPUTS_DATA

GROUP BY IDDEVICE, IDPARK, IDFIELD;

join(LAST_TIME)

LOAD IDDEVICE, IDPARK, IDFIELD,

Max([ENERGY YELDED]) - Min([ENERGY YELDED]) as CURR_ENERGY_TODAY

RESIDENT GENERAL_INPUTS_DATA

WHERE floor(DATETIME) = floor(today())

GROUP BY IDDEVICE, IDPARK, IDFIELD;

Re: JOIN in temporary table

Anonymous — Fri, 15 Apr 2016 13:36:02 GMT

OMG....