topic Re: Replace Null values with Zeros during mapping? in QlikView

Replace Null values with Zeros during mapping?

poluvidyasagar — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to achieve the following:

Demand File:

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:

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

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:

Part	Category	Day1	Day2	Day3	Day4
61697461	B	10	10	20	15
45807953	B	5	6	8	4
78473295	B	4	4	5	2
80204418	B	0	-	-	-
15353912	B	0	-	-	-
18760526	B	0	-	-	-
87452160	B	0	-	-	-

Desired Result:

Part	Category	Day1	Day2	Day3	Day4
61697461	B	10	10	20	15
45807953	B	5	6	8	4
78473295	B	4	4	5	2
80204418	B	0	0	0	0
15353912	B	0	0	0	0
18760526	B	0	0	0	0
87452160	B	0	0	0	0

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

Re: Replace Null values with Zeros during mapping?

olivierrobin — Tue, 26 Jun 2018 14:14:55 GMT

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

Re: Replace Null values with Zeros during mapping?

sunny_talwar — Tue, 26 Jun 2018 14:18:38 GMT

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' ;

Re: Replace Null values with Zeros during mapping?

poluvidyasagar — Tue, 26 Jun 2018 14:19:00 GMT

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.

Re: Replace Null values with Zeros during mapping?

poluvidyasagar — Tue, 26 Jun 2018 14:19:47 GMT

Hi Sunny,

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

Re: Replace Null values with Zeros during mapping?

sunny_talwar — Tue, 26 Jun 2018 14:25:05 GMT

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

];

Re: Replace Null values with Zeros during mapping?

poluvidyasagar — Tue, 26 Jun 2018 14:36:08 GMT

Thanks Vishwa.. It works

Re: Replace Null values with Zeros during mapping?

sunny_talwar — Tue, 26 Jun 2018 14:37:36 GMT

Vishwa?

Re: Replace Null values with Zeros during mapping?

sunny_talwar — Tue, 26 Jun 2018 14:38:18 GMT

Ok Amarjeet, I am glad it worked

Re: Replace Null values with Zeros during mapping?

poluvidyasagar — Tue, 26 Jun 2018 14:44:24 GMT

lol.. I am sorry Sunny

Re: Replace Null values with Zeros during mapping?

sunny_talwar — Tue, 26 Jun 2018 14:45:01 GMT

No problem brother