topic Re: Calculate difference in same feilds and show delta in QlikView

Calculate difference in same feilds and show delta

sujit_nath — Sat, 16 Nov 2024 04:16:27 GMT

I have a table such as:

name count

java 200

java_1 400

db 500

db_1 400

sql 1000

sql_1 1000

_______________________________________

I want the counts to be displayed as:

name count delta

java 400 +200

db 500 -100

sql 1000 0

__________________________________

Basically taking example of one row, Java

we're calculating java_1-java and showing it in the other table with + sign if count is more, or - sign accordingly.

Re: Calculate difference in same feilds and show delta

raman_rastogi — Tue, 05 Mar 2019 07:47:03 GMT

Try This

Data:
Load * Inline [
name, count
java, 200
java_1, 400
db, 500
db_1, 400
sql, 1000
sql_1, 1000];

Load
RecNo(),
PurgeChar( (name),'_1') as Name,
count-Previous(count) as count
Resident Data where PurgeChar( (name),'_1')=PurgeChar( Previous(name),'_1');
Drop Table Data;

Regards,

Raman

Re: Calculate difference in same feilds and show delta

sujit_nath — Tue, 05 Mar 2019 09:07:42 GMT

Thanks Raman,

But previous wont work here because in actual data, it won't be necessary that the _1 thing would be just above the original data.

Is there a way to identify that from any row in the data and get the difference?

Re: Calculate difference in same feilds and show delta

raman_rastogi — Tue, 05 Mar 2019 09:49:44 GMT

try this one

Data:
Load * Inline [
name, count
java_1, 400
java, 200
db, 500
db_1, 400
sql_1, 1000
sql, 1000];

Data_temp:
Load
name,
PurgeChar( (name),'_1') as Name,
count
Resident Data Order by name ;
Drop Table Data;
Load
RecNo(),
Name,
count-Previous(count) as count
Resident Data_temp where Name=Previous(Name) ;
Drop Table Data_temp;