Qlikview Data Cleanup

[svm_1412]

Hallo everyone!

I am fairly new to Qlikview. Have a question regarding Data Clean-up. 

The below Table shows the values,

Row values marked with the font RED sum up to "Zero", in such cases I want to set the values(that made up the sum) to "Zero".

The combination could be from Bucket_0_30 until Bucket_2880.

Attached is an Excel File explaining it in detail. I  have around 200K Rows, and was not able to find a solution.

Account	Bucket_0_30	Bucket_31_60	Bucket_61_90	Bucket_91_120	Bucket_121_150	Bucket_151_180	Bucket_181_360	Bucket_361_720	Bucket_721_1080	Bucket_1081_1440	Bucket_1441_1800	Bucket_1801_2160	Bucket_2161_2520	Bucket_2521_2880	Bucket_2880	Total Buckets/faellig_gesamt
1	0	0	0	-5,99	0	0	0	0	-4,99	57,81	0	0	-15,61	-37,21	0	-5,99
2	0	0	0	0	0	0	0	0	0	0	0	0	0	-66,13	0	-66,13
3	-339,36	59,98	169,27	0	0	0	0	-19,98	19,98	0	0	0	0	-22,21	22,21	-110,11
4	3,27	2,87	30,28	0	55,1	0	-100	-27,61	-12,39	0	0	0	0	40	0	-8,48
5	1,49	5,28	0,79	1,67	1,02	0	0	2,48	5,25	20,2	0	20,89	-40,62	-99,35	0	-80,9
6	1,57	3,34	3,89	2,59	2,09	7,44	-5,86	2,67	-31,12	4,49	0	0	0	-17,48	17,48	-8,9
7	-933,36	-363,58	-77,88	0	0	1327,27	-1761,93	1761,93	0	0	-891,61	-304,01	1195,62	0	0	-47,55
8	0	0	-20,67	0	0	0	-36,99	2,86	-13,79	47,92	0	0	0	-11,64	11,64	-20,67
9	0	0	0	0	0	0	-31,34	0	17,99	0	0	0	0	-19,94	19,94	-13,35

Thanks in Adv

Regards

Manju

[jensmunnichs]

What's the logic behind summing those specific values? Or are the values  that need to be summed marked red in the source data as well?

[svm_1412]

Hey,

No, the values are not marked with Red. I used it to frame the question so I could explain clearly.

My thought process is: sum of  the combinations should give a ZERO. It can be two combinations, or any number of combinations. 

But its always Continuous. I mean, there may be 0 in between the values(like in the first row below), but they are always Continuous.

Bucket_0_30	Bucket_31_60	Bucket_61_90	Bucket_91_120	Bucket_121_150	Bucket_151_180	Bucket_181_360	Bucket_361_720	Bucket_721_1080	Bucket_1081_1440	Bucket_1441_1800	Bucket_1801_2160	Bucket_2161_2520	Bucket_2521_2880	Bucket_2880
0	0	0	-5,99	0	0	0	0	-4,99	57,81	0	0	-15,61	-37,21	0
0	0	0	0	0	0	0	0	0	0	0	0	0	-66,13	0
-339,36	59,98	169,27	0	0	0	0	-19,98	19,98	0	0	0	0	-22,21	22,21

 thx for your time!

[jensmunnichs]

Hey,

PFA

Sorry it took a while, busy weekend.

This was quite a tricky but fun one to do. I'm about 1000% sure there are more efficient solutions to this problem than the one I came up with, but it works! I hope it holds up when using all of your data. Keep in mind that if your real data has more buckets, you'll have to change the script a bit (should be fairly self-explanatory).

I found this video explaining a different logical solution to this problem, though I was unfortunately not able to make it work in Qlikview. If you are able to implement it somehow, it would probably be a lot more efficient than my solution, so I thought I'd post it anyway.

Hope this helps! Let me know if you have any questions about this.

[svm_1412]

Hey Jens!

thx much for the App, its very well Documented and I could understand what its doing in each and every Step.

I connected it with the actual Data, it took around 2 hours on my Local machine but it came up with the output !!!

Looks very good, but there is one small glitch:

I found that there is a difference in Sum (Buckets Total) between that of Excel and that of Qlikview(around 100.000 € in Total for 200k Rows). Looked up deeper and could find some Rows, that have a difference in SUM.

In the attached Sample I have included some Rows, Sample Account: 13071, 31085, 46627  (highlighted in Yellow).

For Ex. In the Row(13071) it has to sum up into -1,5 but turns out to be 0 in Output. And I could not figure out what exactly is making it to 0.

Thanks & Regards

[jensmunnichs]

Hi there,

I think this is the result of checking all continuous combinations before replacing. If you look at columns K through P (for 13071), you'll see that those values sum up to be 0 as well, so those values get replaced too.

I'm sure it wouldn't be impossible to replace after every check, but the question is, in what order should the rows be checked? Depending on your answer, we might have to change the loop logic a bit as well. For example, I could swap them around so that it first checks every combination of continuous rows with a length of 1 value, then 2, then 3, etc. Or we could stick with what it does now, which is to first check every row of any length starting from the first column, then every row of any length starting from the second column, and so on.

Also, should we check left to right, right to left?

I hope this makes sense, if you have any questions please let me know.

[svm_1412]

Hi Jens,

ach! i understand. That is the reason why its adding up to Zero.

I would say of the Order from Right to Left. Because the first  Open Payment is 238,86 and then the customer paid -240,36 and so forth.

The option with checking every combination of continuous rows with a length of 1 value, then 2, then 3 sounds promising.

So for my understanding,  the loop first looks up for the Subset in RED(beginning from Right), replaces it and then looks up for the next subset(in our case Green= -1,5 ) correct? 

13071

0

0

0

0

0

0

0

0

-1,5

1,5

0

-210,42

210,42

-240,36

238,86

But i cannot wrap my head around how much complexity it would consist.

Thanks and regards

[jensmunnichs]

Hi,

>The option with checking every combination of continuous rows with a length of 1 value, then 2, then 3 sounds promising.

I believe we might actually have to use the other method I described, as I think this would not return the result you described.

>So for my understanding,  the loop first looks up for the Subset in RED(beginning from Right), replaces it and then looks up for the next subset(in our case Green= -1,5 ) correct? 

In this case, if we are checking every row of 1 value, then every row of 2 values, etc., we would actually find and replace -210,42/210,42 and -1,5/1,5 before finding the row you marked in red (as this row consists of 6 values), even if we are going right to left.

I'll therefore try to make it so we check every row starting from the final column, then every row starting 1 column in, then 2, etc. No promises yet but I'll see if I can make it work. One thing I can already tell you though is that it will probably perform worse than the previous app I posted, but we'll just try to get the logic right first and worry about performance later.

[jensmunnichs · Accepted solution]

So this ended up being a bit easier than I thought it would be, though again I'm not sure about the performance of the new app.

Changelog:

• Used your Excel sheet instead of Inline data
• FlagVar (and everything related to FlagVar) removed, no longer needed
• Loops reversed, now start at 15 and work their way back to 1 ('step -1') to make sure we read right to left
• $(b) is now the first bucket in the row, $(a) is the last (used to be flipped)
• CurFlag variable removed, as we now only ever have 1 flag field at a time
• Flagged values are now replaced immediately after flagging, so that the old values are no longer considered in flagging remaining values

The results I got seemed to all have the same totals as before, so I think this has been a success. I will say though that when I compare the values using an Excel formula, I'm getting some 'FALSE's returned, so I think the formatting might be a bit off. However, this should easily be fixed by reloading the data using formatting functions (like text() or num()).

Hope this now does what you were looking for, if not let me know!

[svm_1412]

Hey,

Perfekt! does exactly what I was looking for.

And the Performance is better than the first, it took around 40 min for 200k+ Rows.

Ya, there was a formatting error in Excel, i corrected them and I have an exact match.

Thank you so much for your time.

Regards,

svm