Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
This question have probably been answered, but I have not been able to implement the answers I found, in the data I am using. Hopefully, someone here can help me.
I have the following data.
Table_example:
Load * inline
[ID,created,value
73241001,13-04-2018 00:04,96
73241001,16-04-2018 00:04,108
73241001,16-04-2018 00:04,109
73241001,16-04-2018 00:04,110
73241001,19-04-2018 00:04,137
73241001,21-04-2018 00:04,138
73241001,22-04-2018 00:04,142
73241001,23-04-2018 00:04,165
73241001,24-04-2018 00:04,177];
The values in my example are already accumulated by date from another Qlik script.
Were all values are added to each other from the first date, so the values I really have is:
Table_raw_data:
Load * inline
[ID,created,value
73241001,13-04-2018 00:04,0
73241001,13-04-2018 00:04,96
73241001,16-04-2018 00:04,14
73241001,16-04-2018 00:04,1
73241001,16-04-2018 00:04,1
73241001,19-04-2018 00:04,27
73241001,21-04-2018 00:04,1
73241001,22-04-2018 00:04,4
73241001,23-04-2018 00:04,13
73241001,24-04-2018 00:04,12;
I have a API that does not accept multiple values on the same date.
So what I need is something like this:
Concatenate
Table_final:
LOAD *,
ID,
created,
value,
//if multiple CREATED exist, subtract VALUE from previous CREATED as SUBTRACTED,
//accumulate all SUBTRACTED by CREATED
Resident Table_example;
What is your expected output from Table_raw_data?
The expected outcome of the table Table_raw_data, is Table_example.
That is my accumulation of the Table_raw_data, so I have accumulated values.
Could you explain a bit more about the calculation logic? Also I see there are multiple values for single date 1'6-04-2018 00:04' in your target table, but you needed one value for every dates, right?
Could you explain a bit more about the calculation logic?
Here is my script. I hope it is understandable.
NoConcatenate
Table_example:
load
*,
Num(If("ID"=Peek("ID"), "value"+Peek([Running Total]), "value")) as [Running Total]
Resident table_raw_data where created>=year(today()-1) Order by "ID", created ;
Also I see there are multiple values for single date 1'6-04-2018 00:04' in your target table, but you needed one value for every dates, right?
Yes, I only need all the values from the same date, added together.