Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
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.