How can I show a zero loan balance in my object, w... - Qlik Community

sharynwhite · ‎2018-12-03

The logic from a few of my nested if statements result in no loans falling into my specified loan categories. As a result, these loan categories simply do not show up at all in my object. However, I still need it to show as a zero balance (according to my defined expressions inside the QlikView).

EXAMPLE:

if(([Regulatory Report Code]=4 or [Regulatory Report Code]=5) and [Purpose Code]=79,'2107', )

.......... no loans fall into this if statement ....... so 2107 does not show in my object.

I need to reflect 2107 inside my chart with a zero loan balance.

hopkinsc · ‎2018-12-03

Hi, the first thing to check is that you are not suppressing zeros in the chart (under the presentation tab)

othniel2014 · ‎2018-12-03

I would say, that you should inject data '2107' with zero value with a group by dates

hopkinsc · ‎2018-12-03

might be a silly question, but if you were to select [Purpose Code]=79 manually in a list box, then have another list box for [Regulatory Report Code], are there white values for 4 and 5?

rwunderlich · ‎2018-12-03

Is this a calculated Dimension?

-Rob

sharynwhite · ‎2018-12-04

Hi,

No , 4s and 5s are grayed out. So no loans fell into this category for the specified time line. However, I still want to show '2107' in my chart, but as a zero balance or even just an empty field as long as I can show '2107' in my chart. Thanks !

sharynwhite · ‎2018-12-04

Hello,

I am not suppressing zeros. In fact, I have changed the Null Symbol to $0.00 and still nothing. I believe the issue is that because no loans fall into this category that there are no 'zeros' or 'null' values. It is that there aren't any loans in '2107' category. I just want to show '2107' as empty so to speak.

hopkinsc · ‎2018-12-04

In that case i think you need to add in zero's then as already suggested by @othniel2014

othniel2014 · ‎2018-12-05

I really do not know what tables you have, what diagram, or where you are applying that condition. But if your problem is to filter a value that does not exist on any date, the most advisable thing is to feed it into the data.

La verdad no sé qué tablas usted tenga, ni que diagrama, ni donde esta aplicando esa condicion. Pero si su problema es filtrar un valor que no existe en ninguna fecha, lo mas recomendable es alimentarlo en la data.

Example:

Data:
LOAD * INLINE [
    Account, Description, Value, DateField, RegulatoryReportCode, PurposeCode
    1-111, aaaaa, 50, 01/12/18, 1, 80
    1-112, loan, 40, 01/12/18, 4, 79
    2-123, bbbbb, 20, 01/12/18, 3, 80
    3-123, cccccc, 10, 01/12/18, 4, 80
    1-111, aaaaa, 30, 02/12/18, 5, 80
    1-112, loan, 25, 02/12/18, 5, 79
    2-123, bbbbb, 15, 02/12/18, 2, 80
    3-123, cccccc, 55, 02/12/18, 3, 80
    1-111, aaaaa, 24, 03/12/18, 4, 80
    2-123, bbbbb, 44, 03/12/18, 5, 80
    3-123, cccccc, 34, 03/12/18, 1, 80
];

TablaMinMax:
LOAD
min(DateField) as DateMin,
max(DateField) as DateMax
Resident Data
;

LET vMin=num(Peek('DateMin',0,'TablaMinMax'));
LET vMax=num(Peek('DateMax',0,'TablaMinMax'));
LET vTope= 'Max(Total Fecha)';

DROP Table TablaMinMax;

MasterCalendar:
LOAD
Date(IterNo()+$(vMin)-1)   as DateField,
month(Date(IterNo()+$(vMin)-1)) as Mes,
year(Date(IterNo()+$(vMin)-1)) as Año,
MonthName(Date(IterNo()+$(vMin)-1)) as MesAño,
IterNo()+$(vMin)-1 as NumFecha
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);


Outer Join (Data)load 
'2107' as Account,
'Test' as Description,
0 as Value,
DateField,
Null() as RegulatoryReportCode,
null() as PurposeCode
Resident MasterCalendar
////once a month
//Where DateField = MonthEnd(DateField)
;

How can I show a zero loan balance in my object, when no loans fall into my if statement logic ?