topic Re: Qlikview Looping Issue in QlikView

Qlikview Looping Issue

Anonymous — Wed, 22 Nov 2017 07:51:13 GMT

Hi All,

I am facing an certain issue for an new development requirement . the requirement is as follows.

Have an Screenshot of the sample data. Here for the same Id we are getting about 5 lines of data and all of them have an different plate No's. if the plate flag is yes then it is generic or else normal.

If the values of Received Order Quantity is equal to the SAP Order Quantity then those rows should not be shown in the report. The Problem is that inside the Same ID they are segregated like this so even if the value of Generic and Normal are equal to the SAP one both should be ommited.

Like here if you add the Value of the Received Order Quantity in first row and the Fourth row it becomes 45 and both should be ommited. Similarly for second and fifth both should be ommited.

So what i thought was to create an while loop based on the Count of Plate Flag = 'Yes' and then add the values based on it. But even if it gets looped two times how can we specify it should sum only Generic and Normal and not like Generic- Generic or Normal - Normal.

Also there is one more problem if we create an loop also then for some orders if there is no generic flag at all it will not go into the loop. If there if no generic Plate and there are three normal plates then none of the logic should apply and all three should come on the report even if the sum of any two or all three is equal.

EDIT:

1) Here first and fourth are all same and if you add ROQ we get SAP Order Quantity so those should be omitted.

2) Similarly second and Fifth.

3)Third should be available in the report.

4) Similary sixth and seven when it is added becomes equal and should be elimanted.

5) Eight and nine both id and invoice id is different so should be there in the report.

Thanks in Advance for your Reply.

- Vigneshwaran.M.K

Message was edited by: Vigneshwaran MK

Re: Qlikview Looping Issue

petter — Wed, 22 Nov 2017 08:00:42 GMT

A couple of questions:

1) With "The Problem is that inside the Same DC...." do you mean "......same ID"?

2) Could you please provide some sample data that is relevant and more than a few lines in an Excel-file or CSV-file?

Re: Qlikview Looping Issue

Anonymous — Wed, 22 Nov 2017 09:39:04 GMT

Hi Petter,

Thanks for your reply. I have attached an sample and have also put in the various scenarios in the original post itself.

It was an mistake from my end only. Same DC means Same ID only.

Re: Qlikview Looping Issue

petter — Wed, 22 Nov 2017 10:56:15 GMT

Quick question:

Is it always two rows that should be considered and added together and then be compared to the SAP Order Quantity?

Re: Qlikview Looping Issue

Anonymous — Wed, 22 Nov 2017 11:02:50 GMT

Hi Petter,

Not Neccesarily. If the ID, Invoice ID and Date is same with different plate Number and has atleast one plate flag as Yes, then only it should be considered.

If it is just one Row it will automatically be compared with SAP quantity and if it is equal then it will be eliminated. which is working correctly. Only when it is splitted based on the flag we are having these issues.

Re: Qlikview Looping Issue

Anonymous — Wed, 22 Nov 2017 11:04:34 GMT

It can be more than two also like in the case of first five rows all the three (ID, Invoice ID and Date) are same. But still compared based on the flag

Re: Qlikview Looping Issue

petter — Wed, 22 Nov 2017 11:10:15 GMT

But can a sum of the rows be a situation that might occur?

Like one with plate flag Yes and two with plate flag No? Is the processing rule to always only add two and only two numbers to check for the SAP Order Quantity?

Is there any limitation to how many rows that can occur for each ID? A max number of rows?

Re: Qlikview Looping Issue

Anonymous — Wed, 22 Nov 2017 11:37:32 GMT

There is No limitations for ID. It can have even 10 rows tagged to it. If all the values has Flag as 'No'. Then all the rows should come.

Else if there are suppose 10 rows and there are about 2 rows which has Plate Flag as No. Then we should check if the value of that Plate Flag No if added with any one of the other eight rows with Plate Flag Yes gives the values of SAP Order Quantity. If So both the rows should be removed else both should be present.

Here's where the tricky part even if the values of two rows which has No are added and becomes equal to SAP value it should not be neglected. Similarly for 'Yes' as well.

Only if it is an 'No' and an 'Yes' it should be taken into consideration. Also suppose there are two 'No' and Two 'Yes'. Adding the Two 'No' and One 'Yes' will give the SAP Value then all three should be eliminated.

Re: Qlikview Looping Issue

petter — Wed, 22 Nov 2017 13:11:09 GMT

To be able to calculate this reliably you will have to narrow down the constraints quite a bit. I can't help you - sorry.

For example if you have 1 YES and 19 NOs you will have to make a combination of the various numbers to sum up in population sizes varying from 2 up to 20. By simple combinatorics that would calculate to over a million different sums to evaluate for this single ID. For 1 YES and 7 NOs the number of calculations will be 247.

It is not a straightforward calculation - so I think it will be very timeconsuming to find a right solution both in load script or in a visualization in a table.... At least it will be for me.

Good luck.

Re: Qlikview Looping Issue

Anonymous — Wed, 22 Nov 2017 14:50:16 GMT

Thanks for your reply. Will post here if i find a solution 🙂