topic Re: Calculation of an Account Balance? in QlikView

Calculation of an Account Balance?

Anonymous — Mon, 23 Apr 2018 00:04:39 GMT

LOAD * INLINE [

Acct, Balance, SalesPerson

1212, 100, David

1212, 100, Mike

1212, 100, Lucy

1525, 325, Jack

1525, 325, Bill

1525, 325, Edgar

1630, 100, Paul

];

// In the preceding, I want only the Balance in the FIRST line of each Acct

// to be used in calculating a ClosingBal for each Acct. I.E., for Acct 1212, the Balance is 100

//The calculation I need is the (Acct Balance) - count(SalesPerson) = ClosingBal where ClosingBal

//is the starting Balance for the next row in the Acct.

//The Report would look like the following:

Acct Balance SalesPerson ClosingBal

1212 100 David 99

1212 99 Mike 98

1212 98 Lucy 97

1525 325 Jack 324

1525 324 Bill 323

1525 323 Edgar 322

1630 100 Paul 99

// I only have QlikView Personal so I cannot process a 3rd party .qvw solution. Anybody kind

// enough to help me would have to print out the solution. I have spent two days on this with

// all kinds of approaches but still not got it. Thanks David

Re: Calculation of an Account Balance?

campbellr — Mon, 23 Apr 2018 06:20:23 GMT

Hi David

If you're wanting to achieve this in the load script, the following works:

tmp:

LOAD * INLINE [

Acct, Balance, SalesPerson

1212, 100, David

1212, 100, Mike

1212, 100, Lucy

1525, 325, Jack

1525, 325, Bill

1525, 325, Edgar

1630, 100, Paul

];

NoConcatenate

SPCount:

Load

if(Acct=Previous(Acct),BalancePrev - AcctCountRT - 1, Balance -1) as BalanceClose

,if (Acct=Previous(Acct), Balance - AcctCountRT ,Balance) as Balance

,Acct

,SalesPerson

;

Load

if (Acct=Previous(Acct), 1 + previous(AcctCount),0) as AcctCountRT

;

Load

Acct

,SalesPerson

,if(isnull(Previous(Balance)),Balance,Previous(Balance)) as BalancePrev

,Balance

,if (Acct=Previous(Acct),1,0) as AcctCount

Resident tmp;

Drop table tmp;

Re: Calculation of an Account Balance?

Anonymous — Mon, 23 Apr 2018 13:05:30 GMT

Hi Ron:

It works perfectly. I am now trying to digest how and why it works. I am a rookie at QV and have lots to learn. You have been a great help Sir and you are obviously no rookie!

Thanks

David

Re: Calculation of an Account Balance?

Anonymous — Mon, 23 Apr 2018 13:49:03 GMT

Hi Ron,

I understand the use of Resident Tables. What is troubling me are the [3] separate LOAD statements under your SPCOUNT: in the solution you have provided.

I thought that any LOAD statement had to be introduced under a corresponding new (Table:) name. In your solution, this is not the case and I have not encountered this anywhere else. Can you please explain how/why this is being done in my specific case? Or, refer me to any reading that would cover such syntax.

Thanks again

David

Re: Calculation of an Account Balance?

campbellr — Mon, 23 Apr 2018 23:13:29 GMT

Hi David

The multiple loads are what Qlik folk refer to as "preceding loads". There is lots written about it so I won't go into the nitty gritty here. The short version is they work from the bottom up and allow you to build calculated fields at one level then refer to that field in the next level up.

To make the script a little more bullet proof it would probably be best to include an "order by" line in the resident load to sort the data by the acct field, as it is if you were to add a new line at the end that had the same acct as the first line it would treat it as a new acct.

What you were asking to do is simple in Excel world where there is intention to calculate values based on other rows of data. I've had trouble in the past with Qlik trying to produce a running total with multiple sort fields involved, it's possible but far more difficult than it should be.

Glad it does what you were after. If you can share a desired outcome I (or someone else) might be able to suggest another way to get there.

Ron

Re: Calculation of an Account Balance?

Anonymous — Mon, 23 Apr 2018 23:39:52 GMT

Ron, when I added 2 more records to ACCT 1212 in the InLine sample, the report becomes incorrect. Specifically, if you run the following, the balance forward on line 4 of the 1212 ACCT is not correct. Can you please take a look.

Thanks David

tmp:

LOAD * INLINE [

Acct, Balance, SalesPerson

1212, 100, David

1212, 100, Mike

1212, 100, Lucy

1212, 100, Henry

1212, 100, Joshua

1525, 325, Jack

1525, 325, Bill

1525, 325, Edgar

1630, 100, Paul

];

NoConcatenate

SPCount:

Load

if(Acct=Previous(Acct),BalancePrev - AcctCountRT - 1, Balance -1) as BalanceClose

,if (Acct=Previous(Acct), Balance - AcctCountRT ,Balance) as Balance

,Acct

,SalesPerson

;

Load

if (Acct=Previous(Acct), 1 + previous(AcctCount),0) as AcctCountRT

;

Load

Acct

,SalesPerson

,if(isnull(Previous(Balance)),Balance,Previous(Balance)) as BalancePrev

,Balance

,if (Acct=Previous(Acct),1,0) as AcctCount

Resident tmp;

Drop table tmp;

Re: Calculation of an Account Balance?

campbellr — Tue, 24 Apr 2018 06:07:04 GMT

Hi David

take 2, have tested this and it works for your data and includes a sorting to keep the entries with the same acct together.

So I've added in a row count to use in the calculation of how many salespeople exist in the list and use that to subtract from the balance.

The mapping load is to assign the row count of the first line for each account. I had tried this as a preceding load but the rowNo() function doesn't work with a preceding load.

tmp:

LOAD * INLINE [

Acct, Balance, SalesPerson

1212, 100, David

1212, 100, Mike

1212, 100, Lucy

1212, 100, Henry

1212, 100, Joshua

1525, 325, Jack

1525, 325, Bill

1525, 325, Edgar

1630, 100, Paul

];

Join (tmp)

Load

,RowNo() as rowCount

Resident tmp

Order By Acct, SalesPerson;

MapFirstRow:

Mapping

Load

Acct,

rowCount

Resident tmp

Order By Acct, rowCount;

NoConcatenate

SPCount:

Load

if (Acct=Previous(Acct),Balance - AcctSPNum,Balance) as Balance

,if (Acct=Previous(Acct),Balance - AcctSPNum - 1,Balance-1) as BalanceClose

,Acct

,SalesPerson;

Load

rowCount - AcctFirstRow as AcctSPNum

,*;

Load

Acct

,ApplyMap('MapFirstRow',Acct) as AcctFirstRow

,SalesPerson

,if(isnull(Previous(Acct)),Acct,Previous(Acct)) as AcctPrevious

,Balance

,rowCount

Resident tmp

Order By Acct,rowCount;

Drop table tmp;

Re: Calculation of an Account Balance?

Anonymous — Tue, 24 Apr 2018 12:22:01 GMT

Good Morning Ron:

Obviously, I have a lot to learn. I am very grateful for your efforts and must "study" carefully what you have done. I am of course adapting this InLine model to a bigger problem where the concept applies over hundreds of thousands of records. The syntax is always a struggle for me, compounded by how and why one approach makes more sense over another. I have purchased most of the QV books and been through a lot of examples on YouTube and am slowly grasping the basics. The 'balance forward' calculation type of problem and your solution to it gives me a lot to think about and digest. So now, and I admit this, I will adapt what you have supplied to my business problem without truly understanding all of its complete ramifications. I may have to come back to you with a few questions specific to your solution when I get this thing underway. Again, I am much obliged and think that this QV Community is one of the best things I have discovered. Best regards David.

Re: Calculation of an Account Balance?

campbellr — Wed, 25 Apr 2018 22:36:38 GMT

Hi David

I'm happy to explain in more depth any of the bits that are causing concern. The preceding loads and ApplyMap() functions are two things I have found very useful recently. ApplyMap has a distinct advantage over joining two tables in that it does not introduce duplication. It works like a vlookup in Excel in that it returns the value associated with the first match between the map and the data. Also the mapping table is automatically dropped after the load is complete.

Re: Calculation of an Account Balance?

Anonymous — Thu, 26 Apr 2018 22:37:25 GMT

Hi Ron:

I have applied your solution to the big picture and it works very well. I do need to understand the how/why it works and will go through it as it is important to see the logic behind it all. Right now, and with another post, I am having trouble with a Dynamic Calendar creation to view some of the data you have helped me with. There is a great video on 'dynamic calendar' creation/usage at Selecting Arbitrary Date Ranges - YouTube. I have implemented this approach in a few other projects and it works well. But on this recent project and after I had it working with one specific date field, I tried to switch it over to another date field. Now, it doesn't work and the Selections Box does not pick up the date ranges I am specifying. I think that the "Trigger" function is at the root of this problem. But strangely enough, and to try to eliminate any conflicts, I did a dummy creation of another .qvw and had no luck with it either. I can call anywhere in North America if you think an actual conversation would be useful. I am in Toronto and if you would not mind I can call you at your convenience. I have Team Viewer on my PC if that would be of any help. Regardless, I sure have appreciated your help!

Re: Calculation of an Account Balance?

campbellr — Fri, 27 Apr 2018 00:29:39 GMT

Hi David

I found this when looking at calendars: Better Calendar Scripts | Qlikview Cookbook

It is an update on the calendar build that is standard with qlik training. makes sense.

It has the advantage of linking to a field name only and not a specific table to get the date range.

I make a field I call KeyDate and I have the calendar generate that as a field, as opposed to order date. I then load the date field in my data I want to filter on as KeyDate. I work with data with a number of date fields so choose just one.

Something to keep in mind is the calendar can only be used on one date field in an application. I have heard of ways around this but have not internalised how they work so for me one calendar belongs to one date field.

As for phone call / team viewer. I'm in Australia so time zone is a consideration. Team viewer is fine, also skype works too. My time zone is UTC +10. Can chat after hours my time. I'm happy to explain my workings.

Re: Calculation of an Account Balance?

Anonymous — Fri, 27 Apr 2018 13:17:57 GMT

Hi Ron:

Did not know about the Calendar date restriction. Found my problem though. The source data on the problem date field was coming in as YYYY/MM/DD with the actual '/' contained in the data fields. This is different from every other time that I have tried to use the technique shown in the video. Was really frustrating that the selected 'from' and 'to' dates were not showing up in the Selections Box and I had thought that somehow with my inexperience with Triggers that I had somehow compromised the Document-Wide properties. I would like to give the Team Viewer approach a try sometime and noting the Time Zone difference between us, it is incumbent for me to play the part of the "night owl". Perhaps over a weekend sometime and when you clearly have nothing better to do!

Cheers

Dave

Re: Calculation of an Account Balance?

campbellr — Sun, 29 Apr 2018 22:30:40 GMT

The joys of imported data that looks like a date but is actually just text. I have found, by a process of frequent frustration, that when importing data it is beneficial to use num() to force numbers to be numbers, text() to force text to stay as text and date() to make dates behave as dates. I've had many issues bringing in data that uses text fields as identifiers that often contain only numeric data. mostly not a problem but if the first character is a 0 it will be lost. However it seems not always does it get lost. Also recently found that this text field contained a number with a decimal point. it was interpreted as a number and a trailing 0 was lost. It's a pain having to specify each field data type but in the long run it saves a lot of time.

Happy to have a team viewer session, maybe a Friday evening for me to fit with a Friday morning for you.

Re: Calculation of an Account Balance?

Anonymous — Fri, 04 May 2018 20:26:43 GMT

HI Ron:

As valuable a resource as you are, I would like to try a TeamViewer connection with you next Friday, May 11th. With your time at 9:30 PM, my time is 7:30 AM. If you are available, I would like to give it a go. My email address is

david.balmer@sympatico.ca

If you could send me your email address, I can give you the TeamViewer ID and password. We both have to have the same version of TeamViewer and I am currently on ver 13. Perhaps we could just go over a few of the functions you provided in the 'balance-forward' solution. I am not sure if Team Viewer allows us to speak over the connection but that would be something to try.

Best regards

David