Replace Null values with Zeros during mapping?

[poluvidyasagar]

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

[olivierrobin]

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

[sunny_talwar]

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

[poluvidyasagar]

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.

[poluvidyasagar]

Hi Sunny,

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

[sunny_talwar · Accepted solution]

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

];

[poluvidyasagar]

Thanks Vishwa.. It works

[sunny_talwar]

Vishwa?

[sunny_talwar]

Ok Amarjeet, I am glad it worked

[poluvidyasagar]

lol.. I am sorry Sunny