topic Re: Picking unassociated values straight table in QlikView

Picking unassociated values straight table

Anonymous — Wed, 14 Dec 2016 06:36:45 GMT

Dear All

I have a report where in there are customer names and transactions shown for each customers.

As per the associative principle the customers who have not done any transactions are not displayed in the report.

However I have a requirement to show even those customers and display '0' in the transactions column for such customers.

Is there any way to achieve this result?

Thanks & Regards

Chintan Gala

Re: Picking unassociated values straight table

asgardd2 — Wed, 14 Dec 2016 07:43:30 GMT

Hello!

For example, I created data model:

Clients:

LOAD * INLINE [

Client_ID,Clien_Name

1,HP

2,Acer

3,Samsung

4,HTC

5,Apple

];

Transactions:

LOAD * INLINE [

Client_ID,DOC_NAME,DOC_DATE,AMOUNT

1,01-1/2016,01.02.2016,5

1,01-2/2016,02.02.2016,1

1,01-3/2016,10.02.2016,5

1,01-4/2016,11.03.2016,10

1,01-5/2016,11.04.2016,9

2,02-1/2016,02.03.2016,7

2,02-2/2016,02.04.2016,2

2,02-3/2016,02.05.2016,7

4,03-1/2016,03.02.2016,6

4,03-2/2016,13.02.2016,1

];

To fix it not choose checkbox "Suppress Zero-Values" in settings :

See attachment file.

Re: Picking unassociated values straight table

Anonymous — Wed, 14 Dec 2016 09:35:26 GMT

Thanks Anton for your reply. The above solution works but only without selections of month and year.

As soon as I select a particular month and year, the transactions which hold 0 values disappear.

I want them to be displayed even on selection of month and year.

Regards

Chintan Gala

Re: Picking unassociated values straight table

jonathandienst — Wed, 14 Dec 2016 10:13:15 GMT

You need to have the customer names and months in separate tables, like this:

LOAD * Inline

[

Cust, Date, Value

1, 2016/12/1, 100

1, 2016/11/1, 75

1, 2016/9/1, 70

2, 2016/12/1, 82

2, 2016/10/1, 44

3, 2016/11/1, 55

3, 2016/10/1, 65

3, 2016/9/1, 45

];

LOAD * Inline

[

Cust, Name

1, One

2, Two

3, Three

];

LOAD

Date,

Month(Date) as Month

Inline

[

Date

2016/12/1

2016/11/1

2016/10/1

2016/9/1

];

Create a table with "Name" (and optionally "Month") as dimensions and Sum(Value) as the expression. For the Name dimension, check the "Show All Values" and "Suppress When Value is Null" options. Make sure Name is the top of the Sort list and use this expression to sort by Name =Only({1} Cust).

No selections:

Month selected:

Re: Picking unassociated values straight table

Anonymous — Thu, 15 Dec 2016 07:36:53 GMT

Dear Jonathan

Many thanks for your solution. it works as needed , only thing missing is for those Transactions where there is 0 the month is not displayed in the report. Is it possible to display the month instead of '- '.

Thanks & Regards

Chintan Gala

Re: Picking unassociated values straight table

jonathandienst — Thu, 15 Dec 2016 08:09:26 GMT

I am not sure is that is possible without altering the data or the sense of the table.

You cannot use a calculated dimension to handle the problem. The '-' here is a "missing", not a null, and the calculation will be ignored for a missing value.

You might need to create dummy zero values, or move the Month to an expression rather than a dimension.

This may be useful: NULL handling in QlikView

Re: Picking unassociated values straight table

Anonymous — Fri, 16 Dec 2016 08:25:10 GMT

Thanks Jonathan! I will try the options suggested by you..