topic Re: data comparison on multiple columns in QlikView

data comparison on multiple columns

vireshkolagimat — Mon, 12 Feb 2018 08:43:42 GMT

Hi All,

Please find the attached sample where i have mentioned the input and output data.

For orderno 1106200001 the sum(orderedqtyactual) should be 11 but i am getting 22 since it is summing the data for both order and return. I need to show data for order since the complete order got returned.

I tried to use the set analysis to filter based on ORDER_TYPE but if i have any orders with only return as ORDER_TYPE then i will miss that order no.

Let me know for any clarifications.

Regards,

Re: data comparison on multiple columns

YoussefBelloum — Mon, 12 Feb 2018 08:55:53 GMT

Hi,

If you need to show data for orders that have been returned, you can make calculation for Orderno that have a "RETURN" line:

Sum( {<ORDER_TYPE={'RETURN'}>} orderedqtyactual)

Re: data comparison on multiple columns

vireshkolagimat — Mon, 12 Feb 2018 09:06:01 GMT

Hi, Thanks for suggestion but if i try above method, i loose the qty against the Order. Basically i am showing how many qty was ordered and how many delivered. Here the problem is we have the same orderno for both order and return.

Re: data comparison on multiple columns

YoussefBelloum — Mon, 12 Feb 2018 09:39:50 GMT

you can try this on your output table:

dimensions: ORDERNO, LINE_NO

Expression: aggr(sum(ORDERQTYACTUAL),ORDERNO,LINE_NO,ORDER_TYPE)

Re: data comparison on multiple columns

vireshkolagimat — Mon, 12 Feb 2018 10:41:47 GMT

Hi, yeah it is working fine but what if i have duplicate line_no? can i make use of distinct to get unique Line_no.

Thank you.

Re: data comparison on multiple columns

YoussefBelloum — Mon, 12 Feb 2018 10:49:59 GMT

you have already duplicate Line_no but with different order_type right ?

try with more data and see what you have as an output.

Re: data comparison on multiple columns

vireshkolagimat — Mon, 12 Feb 2018 14:06:01 GMT

Hi, will get back to you. Thanks