Add Zeros for missing data in a Pivot table

[jhew1234]

Hello, 

I am trying to replace the '-' with Zeros. I have looked around and tried alot of surgestions but nothing has worked yet. 

Does anyone know how to do this? 

I've very new to Qlik so the more you could spell it out the better... 

Thanks!! 

[PrashantSangle]

try with if()

if(len(trim((your_expresion))=0,'0',your_expression)

can you paste your current expression?

[jhew1234]

My current expression is Count([Material Number]) .

When I try your solution is give error: Len takes one parameter ? 

[Steven_Haught]

@jhew1234 

Have you tried adding the 0 in the load? You can do a resident or preceding load and possibly add a If(IsNull("Material Number"), 0, "Material Number") as "Material Number

[jhew1234]

Hi Steven, 

Could you please elaborate on a resident or preceding load? 

I have tried doing this in a load statment but it hasnt worked, the field name is MATNR as its from Sap: 

[Sales Document: Item Data]:

LOAD VBELN,
POSNR as Item,
IF(isNull(MATNR),0,MATNR) as [Material Number],
ARKTX as Description,
ZMENG as [Target quantity in sales units],
WERKS as Plant,
ERDAT as [Loading Date],
FIXMG as [Delivery date and quantity fixed],
PRCTR as [Profit Center]

where WERKS = '001'
and
PRCTR = 'BBVX'
and
FIXMG = 'X'
;

SELECT VBELN,
POSNR,
MATNR,
ARKTX,
ZMENG,
WERKS,
ERDAT,
FIXMG,
PRCTR

FROM VBAP;
// STORE * FROM [VBAP] INTO [LIB://FolderConnection/VBAP.qvd];
// DROP TABLE [VBAP];

[Steven_Haught]

@jhew1234 

At the top of that Section of data in the load editor, name that table something like StartData:

Then in a new section in the load editor create a resident load by doing the following:

NewData:

Load

*,

If(IsNull("Material Number"), 0, "Material Number") as "Material Number

Resident StartData;

Drop Table StartData;

[jhew1234]

The error occurred here:
NewData: Load *, If(IsNull("Material Number"), 0, "Material Number") as "Material Number" Resident StartData




I've added it to the top but get the above error, have I set it up right?


NewData:
Load
*,
If(IsNull("Material Number"), 0, "Material Number") as "Material Number"
Resident StartData;
Drop Table StartData;

[Sales Document: Item Data]:

LOAD VBELN & ':' & POSNR as [Sales Order],
POSNR as Item,
MATNR as [Material Number],
ARKTX as Description,
ZMENG as [Target quantity in sales units],
WERKS as Plant,
ERDAT as [Loading Date],
FIXMG as [Delivery date and quantity fixed],
PRCTR as [Profit Center],
NETWR as [Sales Value]

where WERKS = 'GB01'
and
PRCTR = 'KB_SPARES'
and
FIXMG = 'X'
;

SELECT VBELN,
POSNR,
MATNR,
ARKTX,
ZMENG,
WERKS,
ERDAT,
FIXMG,
PRCTR,
NETWR

FROM VBAP;
// STORE * FROM [VBAP] INTO [LIB://FolderConnection/VBAP.qvd];
// DROP TABLE [VBAP];

[Steven_Haught]

@jhew1234 

Lets do this a little different. I was trying to say place this script in a whole new section in the load. 

Press that Plus sign to add a new section in the load editor. In that new section lets put the following script: 

NewItemData:
Load
*,
If(IsNull("Material Number"), 0, "Material Number") as "Material Number"

Resident [Sales Document: Item Data];
Drop Table [Sales Document: Item Data];

[jhew1234]

Hi Steven, 

Thanks, get what you mean now. 

I have added a new load section which is under my table and added the script, 

I ran the load script and then it came up with this

[Steven_Haught]

@jhew1234 

In your first table name don't do the rename of the Material number, leave it as is. Then in the second table, change Material number in the formula to your name in the first load. Should read as follows: 

If(IsNull(MATNR ), 0, MATNR ) as "Material Number"