Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

JOIN in temporary table

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


  

IDPARKIDDEVICEIDFIELDCUR_TIME
1a115/04/2016 12:00
1b115/04/2016 11:00
2c113/04/2016 06:00
2d115/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

  

IDPARKIDDEVICEIDFIELDENERGY YELDED
1a150,00
1b120,00
2c10,00
2d113,00

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

   

IDPARKIDDEVICEIDFIELDCUR_TIMEENERGY YELDED
1a115/04/2016 12:0050,00
1b115/04/2016 11:0020,00
2c113/04/2016 06:000,00
2d115/04/2016 12:0013,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;

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

3 Replies
swuehl
MVP
MVP

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;

Kushal_Chawda

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;


Anonymous
Not applicable
Author

OMG....