topic Re: Generate unknown data in QlikView

Generate unknown data

rennesia — Fri, 18 Mar 2016 14:03:47 GMT

There are two tables with process data measured at different time stamps. I have to make a combined table where for all time stamps data must be present. See table below: the Common table is the 'combined table'. The bold values are the real registered one.

Is there an easy way to realize this?

Re: Generate unknown data

Gysbert_Wassenaar — Fri, 18 Mar 2016 16:00:22 GMT

Temp:

LOAD Timestamp, Temperatures FROM ...;

Concatenate(Temp)

LOAD Timestamp, Pressures FROM ...;

Result:

Noconcatenate

LOAD

Timestamp,

Alt(Temperature,peek(Temperature)) as Temperature,

Alt(Pressure, peek(Pressure)) as Pressure

Resident Temp

Order By Timestamp

;

Drop Table Temp;

Re: Generate unknown data

agustinbobba — Fri, 18 Mar 2016 18:02:26 GMT

Hi Automation,

Try use Outer Join.

CommonTable:

LOAD

Timestamp,

Temperatures

FROM Temperatures;

Outer Join(CommonTable)

LOAD

Timestamp,

Pressures

FROM Pressures;

Best regards!

Agustin

Re: Generate unknown data

MarcoWedel — Sat, 19 Mar 2016 00:01:17 GMT

Hi,

maybe using a join rather than concatenating the source tables to avoid double rows in case some temperature and pressure values are logged using the same timestamp.

regards

Marco

Re: Generate unknown data

MarcoWedel — Sun, 20 Mar 2016 00:20:24 GMT

maybe like this:

tabProcessDataTemp:
LOAD * Inline [
Timestamp, Temperature
11:45:00, 45
11:55:00, 47
12:05:00, 49
12:10:00, 48
12:15:00, 46
];

Outer Join (tabProcessDataTemp)
LOAD * Inline [
Timestamp, Pressure
11:46:00, 4.56
11:58:00, 5.32
12:10:00, 4.36
12:22:00, 3.85
];

NoConcatenate

tabProcessData:
LOAD Timestamp,
    If(Temperature,Temperature,RangeSum(Peek(Temperature),0)) as Temperature,
    If(Pressure,Pressure,RangeSum(Peek(Pressure),0)) as Pressure
Resident tabProcessDataTemp
Order By Timestamp;

Drop Table tabProcessDataTemp;

hope this helps

regards

Marco

Re: Generate unknown data

rennesia — Mon, 21 Mar 2016 07:39:12 GMT

Thanks Marco,

This is a good solution for my problem!

Re: Generate unknown data

rennesia — Mon, 21 Mar 2016 07:44:44 GMT

Thanks Gysbert for your solution, it works fine!

Re: Generate unknown data

oknotsen — Mon, 21 Mar 2016 07:46:19 GMT

If your problem is now solved, please flag the correct answer with Correct Answer.

If not, please make clear what part of this topic you still need help with .