topic Re: Sum element from one table divided by time interval from another. in QlikView

Sum element from one table divided by time interval from another.

Anonymous — Mon, 06 Nov 2017 09:52:05 GMT

Hi,

I have two tables in my qlik sense applications.

[table_one]:

Load

[index],

[Value],

[CicleId],

[CicleEnd],

[CicleStart],

[time_stamp];

[table_two]:

LOAD

[id],

[diIndex],

[diStatus],

[time_stamp];

I try to create table where I would have dimension: CicleId, and measurement sum of diStatus for this cicle period.

What I did is

Sum({<table_two.time_stamp={">=table_one.CicleStart"},table_two.time_stamp={"<=table_one.CicleEnd"},table_two.diIndex={15}>}diStatus)

But What I get is for all rows 0 but in total I get some value (145)

How to fix it

Re: Sum element from one table divided by time interval from another.

Anil_Babu_Samineni — Mon, 06 Nov 2017 11:01:40 GMT

Perhaps this? I am assuming, script involves Qualify statement.

Sum({<[table_two.time_stamp]={"=[table_two.time_stamp] >= [table_one.CicleStart]"},[table_two.time_stamp]={"=[table_two.time_stamp] <= [table_one.CicleEnd]"},table_two.diIndex={15}>}diStatus)

Re: Sum element from one table divided by time interval from another.

Anonymous — Mon, 06 Nov 2017 11:11:50 GMT

No, this is still not correct answer. I think you assume, that timestamp in one table and timestamp in second are the same, but they are not

Re: Sum element from one table divided by time interval from another.

Anil_Babu_Samineni — Mon, 06 Nov 2017 11:18:25 GMT

FYI, This should work. But if it won't work that means your data model may effect to load the data points. Will you able to provide sample

Re: Sum element from one table divided by time interval from another.

Anonymous — Mon, 06 Nov 2017 11:29:07 GMT

I am not sure what you mean by provide sample. I already provide, how tables look like.

I can provide screenshots of my application.

I am sum-ing the purple lines.

As you can see, in range from 8.09 to 8.14.50 I have two lines, so the sum should be 2.

Also for next period from 8.14.50 to 8.20.12 I have two lines as well.

Re: Sum element from one table divided by time interval from another.

Anil_Babu_Samineni — Mon, 06 Nov 2017 11:34:22 GMT

Means, You are just sharing layout of schema not the input values. Some where i would think this has format issue

I apologies to review is not clear from my end, It should be like below

Sum({<[table_two.time_stamp]={"=[table_two.time_stamp] >= [table_one.CicleStart] and [table_two.time_stamp] <= [table_one.CicleEnd]"},table_two.diIndex={15}>}diStatus)

Re: Sum element from one table divided by time interval from another.

Anonymous — Mon, 06 Nov 2017 11:59:19 GMT

You are right, there could be a format issue.

I convert it to

Sum({<[table_two.time_stamp]={"=[table_two.time_stamp] >= [Date(table_one.CicleStart, 'YYYY-DD-MM HH:mm:ss')] and [table_two.time_stamp] <= [Date(table_one.CicleEnd, 'YYYY-DD-MM HH:mm:ss')]"},table_two.diIndex={15}>}diStatus)

but it is still not working.

Re: Sum element from one table divided by time interval from another.

Anil_Babu_Samineni — Mon, 06 Nov 2017 12:07:20 GMT

What is the format for

1) [table_two.time_stamp]

2) [table_one.CicleStart]

3) [table_one.CicleEnd]

Re: Sum element from one table divided by time interval from another.

sunny_talwar — Mon, 06 Nov 2017 12:15:25 GMT

I have a feeling that you might need to use if statement rather than using set analysis here.... but I am not 100% sure... can you share some sample data in an Excel file and explain the output you are trying to get?

Re: Sum element from one table divided by time interval from another.

Anonymous — Mon, 06 Nov 2017 13:43:38 GMT

hmmm,... I don't know exactly how to look for that.

The time_stamp is a field from postgres database field TimeStamp.

In Table it is formatted as : 2017-11-06 10:28:40.380000

The CicleStart and End are derivates from time_stamp, like:

(In Load function)

(If(value > 2000 and Previous(value) < 2000, time_stamp, peek('CicleEnd'))) as CicleEnd,

but if I put them in table, they seams to be Numbsers.