topic Re: Subtract data by date, and accumulate data by date - in load script in QlikView

Subtract data by date, and accumulate data by date - in load script

varmekontrol — Sat, 16 Nov 2024 00:30:24 GMT

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;

Re: Subtract data by date, and accumulate data by date - in load script

tresB — Wed, 10 Jun 2020 07:36:22 GMT

What is your expected output from Table_raw_data?

Re: Subtract data by date, and accumulate data by date - in load script

varmekontrol — Wed, 10 Jun 2020 07:39:40 GMT

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.

Re: Subtract data by date, and accumulate data by date - in load script

tresB — Wed, 10 Jun 2020 08:05:37 GMT

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?

Re: Subtract data by date, and accumulate data by date - in load script

varmekontrol — Wed, 10 Jun 2020 08:12:30 GMT

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.