Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Qlikview Data Cleanup

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

svm_1412

Contributor II

2019-07-19
10:41 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Qlikview Data Cleanup

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

- Tags:
- qlikview_scripting

1,643 Views

1 Solution

Accepted Solutions

jensmunnichs

Creator III

2019-07-23
05:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

9 Replies

jensmunnichs

Creator III

2019-07-19
11:15 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

1,627 Views

svm_1412

Contributor II

2019-07-19
11:42 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!

1,616 Views

jensmunnichs

Creator III

2019-07-22
06:17 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,567 Views

svm_1412

Contributor II

2019-07-22
11:34 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,554 Views

jensmunnichs

Creator III

2019-07-22
04:49 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,536 Views

svm_1412

Contributor II

2019-07-23
04:47 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,525 Views

jensmunnichs

Creator III

2019-07-23
04:58 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,522 Views

jensmunnichs

Creator III

2019-07-23
05:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Contributor II

2019-07-23
11:18 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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