Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jhew1234
Contributor II
Contributor II

Add Zeros for missing data in a Pivot table

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!! 

9 Replies
PrashantSangle

try with if()

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

 

can you paste your current expression?

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
jhew1234
Contributor II
Contributor II
Author

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

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

Steven_Haught
Creator III
Creator III

@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
Contributor II
Contributor II
Author

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
Creator III
Creator III

@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
Contributor II
Contributor II
Author

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
Creator III
Creator III

@jhew1234 

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

 

Steven_Haught_0-1600950707918.png

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
Contributor II
Contributor II
Author

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

jhew1234_0-1600961607587.png

 

Steven_Haught
Creator III
Creator III

@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"