Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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
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' ;
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.
Hi Sunny,
yes it should be that.. I posted that wrong. But it still does not give me zeroes but gives blanks.
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
];
Thanks Vishwa.. It works
Vishwa?
Ok Amarjeet, I am glad it worked
lol.. I am sorry Sunny