Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
varmekontrol
Creator
Creator

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

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;

 

 

 

 

 

Labels (1)
4 Replies
tresesco
MVP
MVP

What is your expected output from Table_raw_data?

varmekontrol
Creator
Creator
Author

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.

tresesco
MVP
MVP

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?

varmekontrol
Creator
Creator
Author

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.