Strange Calculation Results

[pnn44794]

Hello:

I'm getting some inconsistent calculation results from my Load script.  The following is a snippet of code from the load script:

Num(If(IsNull([Business Days End 2 END ( without Approvals )]), [Business Days End 2 END ( with Approvals )], [Business Days End 2 END ( without Approvals )]), '#0') as [Actual GTI Days],

LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]), [E2E SLO] + 1) as [SLA Breach Date],

Num(If([Order Complete Date] > LastWorkDate(RangeMax([Order Create Date], [Approval Complete Date]), [E2E SLO] + 1), 1, 0), '#0') as Breach

Sometimes, Breach will be zero or 1 as expected and then for other rows, it is not.  I've attached sample data.  If you look at, for example, rows 9 - 15, I would expect the values to be all zero's for Breach.  In my pivot table chart, I then have an expression column where the calculation is Sum(Breach)/Count([Host Name]).  As you're probably guessing, the calculation is not correct in the chart.  The idea is to calculate the percent delay from the SLA.  As an FYI, while Host Name is a text field, it is used for counting the number of orders, 1 Host Name = 1 Order.

I've also attached export of the pivot table for reference.  I'm thinking, for example, that the % Delay From SLA for VSI in the OS Build section should be somewhere around 20% maybe.  Would I perhaps need to format some of the date columns being used a certain way?  Here are the one's that would be in play and which in the Load script:

Date([Order Complete Date], 'MM/DD/YYYY') as [Order Complete Date]

Date([Order Create Date], 'MM/DD/YYYY') as [Order Create Date],
Date([Approval Complete Date], 'MM/DD/YYYY') as [Approval Complete Date], 

As always, thanks in advance for any and all replies.  All help is appreciated.

[pnn44794]

Wow!  OMG!  I think I may have figured out the problem, though I cannot explain it technically, but I'll try and I'll try to be brief.

So, it dawned on me Sunny, that you were saving the data (spreadsheet) locally.  I've been pulling it from SharePoint.  The person that creates it, creates it as an Excel file FOR SharePoint and not as a regular link to an Excel file.  With that said, when I go into the SharePoint site itself and open the file, it says it's too large to do so and opens it in Excel.

What I did was let SharePoint open it in Excel and then saved it to the DataSources folder.  Once saved, I did a reload and BINGO, I was a winner.  lol  The data then reported as expected.  Have I made sense with this?

Obviously, this is a bit of a problem since it's manual, but at least it works.  So, you gave me several responses that could be marked as the correct answer (the date and breach script calculations and the environment variables).  Any suggestions?  Would you like to investigate this further?  I'm good, but I may have a question around Fractile, but I can start a new discussion for that.

Is this perhaps a Qlikview bug?  In any event, thank you so much for your help and more importantly, your patience.

[sunny_talwar]

So, pulling this from SharePoint was the issue? That sounds a little strange, but I have never pulled data from SharePoint to understand what it might be doing.

Would you like to investigate this further?

Investigate SharePoint issue? I don't really think I can help you with this.

[pnn44794]

Yes, it appears that pulling an Excel file that is specifically set up for Sharepoint sharing, was the issue. I cannot explain why.

As far as investigating further, I was thinking from a Qlikview perspective and it’s interaction with Sharepoint Excel files. However, it was just a thought. I don’t need to go there and I was only offering to do so if you wanted to. All good my friend.

[sunny_talwar]

I would love to know what the difference is. May be pcammaert‌, rwunderlich‌, marcus_sommer‌ or swuehl can give suggestions here?

[rwunderlich]

Perry,

Do you get a different number of rows loading from sharepoint vs loading the local file?

-Rob

[marcus_sommer]

I have never worked with the SharePoint and must admit that I don't know much about it. But if I understand the concept right there doesn't exist excel-files else everything is a website and the data like above mentioned are probably stored in xml-structures. I'm not sure if these xml-structures are identically to the ooxml-structure from the excel-files and if the different ways of accessing the data make any difference.

I think I would start with the suggestion from Rob of looking if there are the same number of records. After this I would try to compare both datasets without any transformations - just checking if the content of each record and field is the same. For this it might be useful to try to reduce the datasets maybe by removing some fields and records to those ones which seem to be the cause of the problem. And if the issue continued a check with an editor like notepad++ might be necessary to identify really each contained char.

With writing this I remember some struggles with web-based (excel)-content in which normal spaces were replaced with chr(160) or some special chars like chr(8203) - zero-white-space - were added. And of course without knowing it most of the matches and string-functions will fail. My solution in this cases was just to use purgechar() and keepchar() to ensure that there only expected chars within the fields.

If I think further I would give a white-listing of chars with keepchar() a shot before analysing the datastructure because it's very easy to implement.

- Marcus

[pnn44794]

Hello Rob / Sunny / Marcus:

I did not check the row count, but will.  I'm in training this morning, so please look for a response late morning (US Eastern) or early afternoon.

[pradosh_thakur]

Hi

Recently Some colleagues of mine faced something similar and it started throwing error and they cant access thiose files for some reason. it was solved by URL encoding. At east that is what they call it.

[pnn44794]

Ok.  I had a short break from the training and did a quick check and I get the same number of rows for both.  6,958.  I'll try comparing the two datasets per Marcus's suggestion using Sublime Text a little later.  I'll look at the character functions Marcus suggested as well (not sure how to use them, but I'll look them up).

[pnn44794]

All:

My apologies for the delay.  I see no differences between the files.  The only difference is where they are pulled from meaning an Excel Online SharePoint pull or a pull from a shared folder on a server.  As Marcus has suggested, I see no special characters either in both files.

With that said, there have been problems over the last several days in getting the file loaded to SharePoint from Business Objects (was not aware of this until today) wherein the entire file would not get loaded due to some type of user lock issue.  What I reference above where I'm essentially happy as all get out that it worked, I had a full file that I downloaded from the SharePoint site and saved to the DataSources folder and then reloaded.

I have changed the link from a Web Files to a Tables link within my Load Script based on this thread https://community.qlik.com/thread/249283 and specifically Sasi Vardhan KV's response on Feb 9.  I have tried that methodology and it works, but the problem now is that there's not a full dataset to pull because of the issue mentioned above.

Gentleman, I know you may or most likely have bigger fish to fry, so I understand if you don't respond or have no other options.  I appreciate the fact that you did respond to begin with and a big thanks to Sunny for engaging you.

Lastly, I may be good in that I'm getting the expected results but not in the manner hoped for, outside of the loading issue mentioned above, but the issue I'm (or was) having sure is weird.  A big thanks to Sunny for all of his help and a big thank you to the rest of you for assistance and responses as well.

rwunderlich, marcus_sommer or swuehl stalwar1‌