topic Re: How to prevent load inaccurate information... in QlikView

How to prevent load inaccurate information...

Wed, 20 May 2015 15:45:02 GMT

Hi everybody!

I just created a backorder app:

Our ideal billing process is for every Cust PO one invoice is created, however that is not happening and sometimes one Customer PO is related with 2 different invoices,showing unreal Back-orders.

Example:

CustPO: 0029075 is related with 2 invoices: SI015361 and SI015362. This causes that the report show unreal BO, for example the first LineNo_"200000" on the report has $1,706 on PO Amount, but 0 invoiced, meaning that the line is consider as a BO, but the second row same item (I7051) and same LineNo_"200000" show $1,706 on PO Amount and $1,706 invoiced NO BO. Hence the first it is not consider a BO, so this should not be shown in the report.

However if the CustPO is related to only one invoice sometimes the PO amount can be 0, that means that was zero inventory so the total line is a BO

I think that writing an IF statement on the script can be a good approach to not show duplicated keys, or a firstsortedvalue, but I do not know how to do so. Any thoughts?

Thank you very much for your help.

Have a fantastic day!

Re: How to prevent load inaccurate information...

Anonymous — Wed, 20 May 2015 16:37:52 GMT

Do you have some criteria to tell which invoice is correct, SI015361 or SI015362?

If you don't care, and just want to use any of them, try this:

table:

LOAD

#CustPO,

maxstring(Invoice) as Invoice

FROM <your data source>;

LEFT JOIN (table) LOAD

#CustPO,

Invoice,

... // all fields you need

FROM <your data source>;

Re: How to prevent load inaccurate information...

Wed, 20 May 2015 16:58:27 GMT

Hi Michael thank you for your prompt response, yes there's a way to identify them :

First, If the combination CustPO & the Line No is equal and second if that combination is related to different Invoices, then the latter is incorrect.

See the attached image below. Both conditions are met, so the second line is incorrect.

I've created a Key called 'KeyDup' that JOINS CustPO&'|'&Line_No

Re: How to prevent load inaccurate information...

Anonymous — Wed, 20 May 2015 17:24:39 GMT

OK Eric,

The key is useful, it will replace #CustPO in my script above. Something is still missing. The "second" line depends on sort order. For this sample data, my script above works.

Re: How to prevent load inaccurate information...

Wed, 20 May 2015 17:46:40 GMT

Yes the information does not load in a particular sort order, do I need to use Order By "THE KEY I CREATED" on my script?? Do you think that will work?

Re: How to prevent load inaccurate information...

Anonymous — Wed, 20 May 2015 17:53:11 GMT

No, because the value of the key is the same for correct and incorrect record.

Re: How to prevent load inaccurate information...

Wed, 20 May 2015 18:00:52 GMT

What do you recommend me to do?

Thanks for your time!

Re: How to prevent load inaccurate information...

Anonymous — Wed, 20 May 2015 18:06:02 GMT

Try the solution I've suggested in my first reply. If it doesn't work - upload a sample of application with explanation what is expected, what is wrong, and why you consider it wrong.

For example, in your last screenshot, you declared that the 2nd line is wrong, but still didn't explain why, except that it is "the second".

Re: How to prevent load inaccurate information...

Wed, 20 May 2015 18:19:02 GMT

I will thank you Michael.