topic Re: Show rows depending on field values in QlikView

Show rows depending on field values

fgirardin — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

Using views from our ERP, I'm trying to make a table that shows the state of our sales

I use documents informations to do so (customer name, item, quantity, price, document type, invoice N°, date, task, ...)

Here is my table

Each item should have 3 documents: Order / Shipment / Invoice

These 3 documents have each a state that I get from the same field (DOC.OPERATION)

It is either DBCD / DBBL / DBFA

Using this table I can check that each order has each document (order, shipment confirmation and finally invoice)
As you can see on my table, I show each state on a different column (using "IF" condition)

What I'd like to do is to filter ONLY items that have a shipment confirmation (DBBL) but NO invoice (DBFA)
This way I can easily check if the customer has received our invoice (I don't need to check if it's paid, only that the process is complete --- Order > Shipment > Invoice)

As the status field is the same I can't find a way to show only items with DBBL state and no DBFA

I tried to use "COUNT", "ONLY", ... but so far it's not working

If someone have any idea to achieve what I need, let me know

Thank you

Re: Show rows depending on field values

Sergey_Shuklin — Mon, 08 May 2017 17:15:28 GMT

Hello, Fabien!

You can try a set analysis to solve this task. Something like this:

sum({<State={'DBBL'>}[Unit Price])

or sum({<State=-{'DBFA'>}[Unit Price]) if you want to exclude DBFA state.

Re: Show rows depending on field values

fgirardin — Mon, 08 May 2017 21:23:20 GMT

Hello Sergey,

Thank you for your help.

If I use the 1st set analysis, I only see the document and price if there is a shipping document (DBBL)

What I would like to see is ONLY the items that have a shipping document (DBBL) but NO invoice (DBFA)
If I have 2 items as such:

ITEM DOC N° Doc.DATE ORDER SHIPPED INVOICE PRICE

Item1 CD0001 01.01.17 DBCD - - 10

Item1 BL0002 01.02.17 - DBBL - 10

Item1 FA003 02.02.17 - - DBFA 10

Item2 CD0004 01.01.17 DBCD - - 10

Item2 BL0005 01.02.17 - DBBL - 10

I would like to show only (in another table for example)

Item2 CD0004 01.01.17 DBCD - - 10

Item2 BL0005 01.02.17 - DBBL - 10

I need to fulfill 2 conditions:

A) Must have an Shipping Document (DBBL)

B) Must have NO Invoice (DBFA)

Thanks again

Re: Show rows depending on field values

Digvijay_Singh — Mon, 08 May 2017 23:37:35 GMT

This should work -

Dimension - All info fields

Expression as -

Only({<ITEM={"=len(Aggr(concat(INVOICE),ITEM))=0 and len(Aggr(concat(SHIPPED),ITEM))>0"}>}PRICE)

Re: Show rows depending on field values

fgirardin — Tue, 09 May 2017 07:09:10 GMT

Hello Digvijay,

Thank you for your help

I can't make it work using your example

Note that the columns ORDER, SHIPPED & INVOICE get data from the same field "DOC.OPERATION"

I use IF statement to create my three columns as 3 separate dimensions >>>

=if(DOC.operation='DBCD', Doc.operation , '')

=if(DOC.operation='DBBL', Doc.operation , '')

=if(DOC.operation='DBFA', Doc.operation , '')

If I try to adapt your expression >

Only({<ITEM={"=len(Aggr(concat(DOC.Operation='DBFA'),ITEM))=0 and len(Aggr(concat(DOC.Operation='DBBL'),ITEM))>0"}>}PRICE)

It does not work

I could replace the operation status by any letter or number. I did this at first to be able to see all operation for each item

I'm starting to think that it wasn't a good idea

Thanks again for your help.

Re: Show rows depending on field values

fgirardin — Tue, 09 May 2017 12:22:55 GMT

I recreated my table and changed the way I show datas

Here it is:

Fields are:

5011635... BCTE-325 DBCD 17/50245 14.03.2017 40 15 00 600

DBBL 17/30331 27.03.2017 40 15 00 600

DBFA 17/402663 27.03.2017 40 15 00 600

I need to show only ITEMS that have DBBL (shipping document) but no DBFA (invoice)

Thank you again for your precious help !

Re: Show rows depending on field values

Digvijay_Singh — Tue, 09 May 2017 13:37:03 GMT

This one works with your new data -

I used below sample from your data, I assumed that empty invoice means no row with DBFA, as below for item 326.

So as per below sample only item should show is 326.

Load * inline [

Ref,Item,Operation,Doc. N°,Doc.Date,Qty,Unit Price,Misc,Total Price

5011635, BCTE-325, DBCD, 17/50245, 14.03.2017, 40, 15, 00, 600

5011635, BCTE-325, DBBL, 17/30331, 27.03.2017, 40, 15 , 00, 600

5011635, BCTE-325, DBFA, 17/402663, 27.03.2017 , 40, 15 , 00, 600

5011639, BCTE-326, DBCD, 17/50245, 14.03.2017, 40, 15, 00, 700

5011639, BCTE-326, DBBL, 17/30331, 27.03.2017, 40, 15 , 00, 700

5011641, BCTE-327, DBCD, 17/50245, 14.03.2017, 40, 15, 00, 600

5011641, BCTE-327, DBBL, 17/30331, 27.03.2017, 40, 15 , 00, 600

5011641, BCTE-327, DBFA, 17/402663, 27.03.2017 , 40, 15 , 00, 600

];

Expression used for Total price as -

Only({<Item={"=Count({<Operation={'DBBL'}>}Operation)= 1 and Count({<Operation={'DBFA'}>}Operation)=0"}>}[Total Price])

Rest of the fields used as Dimensions

Re: Show rows depending on field values

fgirardin — Mon, 29 May 2017 08:59:41 GMT

Sorry for my late response, I was quite busy on other tasks...

Thank you for your help

I tested your file to see if there was any problem with regional settings on my end and it works fine.... sadly it doesn't work with my datas

If I use your example with my fields >

Only({<ART.Article_cle_principale={"=Count({<DOC.Operation_predefinie_code={'DBBL'}>}DOC.Operation_predefinie_code)=1 and Count({<DOC.Operation_predefinie_code={'DBFA'}>}DOC.Operation_predefinie_code)=0"}>}[LIG.Montant_HT])

Item Operation Total Price

(I only switched Unit Price and 1 other expression into Dimensions)

It says the formula is correct but it does not show ANY price...

I tested with this formula:

Only({<ART.Article_cle_principale={"=Count({<DOC.Operation_predefinie_code={'DBFA'}>}DOC.Operation_predefinie_code)=1"}>}[LIG.Montant_HT])

In this case it does not show the price for "BCTE-382_A" as this item only has "DBCD" (order) and no "DBFA" (Invoice)

However, if I try the same formula but with "=0" at the end, nothing shows (all prices are empty)

Any ideas ?

Thanks a lot

Re: Show rows depending on field values

fgirardin — Mon, 29 May 2017 12:55:54 GMT

I think I found out what was the problem.
Actually we don't have a lot of item without invoices. The only we have are at "00.00" because we are waiting to add a price....
I will ask the person creating the invoices to add 1.- as total if we don't have prices so I can see them on my report

Or is there a way to tell QlikView that 00.00 is not a null value ?

Thanks again