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

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

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

9 Replies
Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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

I can provide screenshots of my application.

Screen Shot 2017-11-06 at 12.26.25.png

I am sum-ing the purple lines.

Screen Shot 2017-11-06 at 12.23.16.png

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.Screen Shot 2017-11-06 at 12.28.12.png

Anil_Babu_Samineni

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)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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.

Anil_Babu_Samineni

What is the format for

1) [table_two.time_stamp]

2) [table_one.CicleStart]

3) [table_one.CicleEnd]

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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?

Anonymous
Not applicable
Author

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.