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.

svedesee
New Contributor II

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
MVP
MVP

Re: JOIN in temporary table

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;

3 Replies
MVP
MVP

Re: JOIN in temporary table

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

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;


svedesee
New Contributor II

Re: JOIN in temporary table

OMG....