Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Replace Null values with Zeros during mapping?

Hi,

I am trying to achieve the following:

Demand File:

PartDay1Day2Day3Day4
6169746110102015
4017925520151515
860121365555
458079535684
784732954452

PartMasterFile:

PartCategory
61697461B
40179255A
86012136C
45807953B
78473295B
80204418B
15353912B
18760526B
87452160B

DemandFile:

Mapping load

Part as PartNumber,

Num(Day1,0.0)& '|' & Num(Day2,0.0)& '|' &  Num(Day3,0.0) & '|' & Num(Day4,0.0) as [4DayDemand]

From

[DemandFile];

Part MasterFile:

Load

Part as PartNumber,

Category,

Subfield(Applymap('DemandFile',Day1,0) ,'|',1) as Day1,

Subfield(Applymap('DemandFile',Day2,0) ,'|',2) as Day2,

Subfield(Applymap('DemandFile',Day3,0) ,'|',3) as Day3,

Subfield(Applymap('DemandFile',Day4,0) ,'|',4) as Day4,

From[PartMasterFile]

Where Category='B' ;

Result in Qlikview after load:

TableBox:

Dimensions:

1.PartNumber

2.Day1

3.Day2

4.Day3

5.Day4

Result looks the following:

PartCategoryDay1Day2Day3Day4
61697461B10102015
45807953B5684
78473295B4452
80204418B0---
15353912B0---
18760526B0---
87452160B0---

Desired Result:

PartCategoryDay1Day2Day3Day4
61697461B10102015
45807953B5684
78473295B4452
80204418B0000
15353912B0000
18760526B0000
87452160B0000

So, I would like not have those blanks as dashes but as zeroes. It works only Day1 field using subfiled function in the load? How do  i fix this?

Sorry for such a long post

Thanks,

Vidya

Message was edited by: Vidya Sagar Polu

1 Solution

Accepted Solutions
Highlighted

Re: Replace Null values with Zeros during mapping?

Try this

DemandFile:

Mapping

LOAD Part as PartNumber,

Num(Day1,0.0)& '|' & Num(Day2,0.0)& '|' &  Num(Day3,0.0) & '|' & Num(Day4,0.0) as [4DayDemand];

LOAD * INLINE [

    Part, Day1, Day2, Day3, Day4

    61697461, 10, 10, 20, 15

    40179255, 20, 15, 15, 15

    86012136, 5, 5, 5, 5

    45807953, 5, 6, 8, 4

    78473295, 4, 4, 5, 2

];


PartMasterFile:

LOAD Part as PartNumber,

Category,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 1) as Day1,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 2) as Day2,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 3) as Day3,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 4) as Day4

Where Category = 'B';

LOAD * INLINE [

    Part, Category

    61697461, B

    40179255, A

    86012136, C

    45807953, B

    78473295, B

    80204418, B

    15353912, B

    18760526, B

    87452160, B

];


Capture.PNG

View solution in original post

10 Replies
Highlighted
Specialist III
Specialist III

Re: Replace Null values with Zeros during mapping?

hello

when using subfield, you should use

1 for day1

2 for day 2

ans so on

in your case, you always retrieve value for day1

Highlighted

Re: Replace Null values with Zeros during mapping?

Shouldn't it be this?

Part MasterFile:

Load

Part as PartNumber,

Category,

Subfield(Applymap('DemandFile', Part, 0) ,'|', 1) as Day1,

Subfield(Applymap('DemandFile', Part, 0) ,'|', 2) as Day2,

Subfield(Applymap('DemandFile', Part, 0) ,'|', 3) as Day3,

Subfield(Applymap('DemandFile', Part, 0) ,'|', 4) as Day4,

From[PartMasterFile]

Where Category='B' ;

Highlighted
Creator II
Creator II

Re: Replace Null values with Zeros during mapping?

I am sorry I posted that wrong.. I did use 1 for Day1 and 2 for Day2 in file. Even then the desired result did not show up.

Highlighted
Creator II
Creator II

Re: Replace Null values with Zeros during mapping?

Hi Sunny,

yes it should be that.. I posted that wrong. But it still does not give me zeroes but gives blanks.

Highlighted

Re: Replace Null values with Zeros during mapping?

Try this

DemandFile:

Mapping

LOAD Part as PartNumber,

Num(Day1,0.0)& '|' & Num(Day2,0.0)& '|' &  Num(Day3,0.0) & '|' & Num(Day4,0.0) as [4DayDemand];

LOAD * INLINE [

    Part, Day1, Day2, Day3, Day4

    61697461, 10, 10, 20, 15

    40179255, 20, 15, 15, 15

    86012136, 5, 5, 5, 5

    45807953, 5, 6, 8, 4

    78473295, 4, 4, 5, 2

];


PartMasterFile:

LOAD Part as PartNumber,

Category,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 1) as Day1,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 2) as Day2,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 3) as Day3,

Subfield(Applymap('DemandFile', Part, '0|0|0|0') ,'|', 4) as Day4

Where Category = 'B';

LOAD * INLINE [

    Part, Category

    61697461, B

    40179255, A

    86012136, C

    45807953, B

    78473295, B

    80204418, B

    15353912, B

    18760526, B

    87452160, B

];


Capture.PNG

View solution in original post

Highlighted
Creator II
Creator II

Re: Replace Null values with Zeros during mapping?

Thanks Vishwa.. It works

Highlighted

Re: Replace Null values with Zeros during mapping?

Vishwa?

Highlighted

Re: Replace Null values with Zeros during mapping?

Ok Amarjeet, I am glad it worked

Highlighted
Creator II
Creator II

Re: Replace Null values with Zeros during mapping?

lol.. I am sorry Sunny