Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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
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.
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)
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.
What is the format for
1) [table_two.time_stamp]
2) [table_one.CicleStart]
3) [table_one.CicleEnd]
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?
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.