Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Gud mrng/evng friends, I have an Issue expecting solution from any one of our members.
I have a Dimension with Rate which consists of 1,2,3,4,0,N/A and blank.
Rate |
1 |
2 |
3 |
4 |
0 |
N/A |
While loading can is it possible to show as below because we have different sources same as Rate but we are getting data in A,B,C,D..respectively.As we have 3 sources ae are associating with Rate causing issues.
Can any possibility while loading prior into Qlik the Rate values can be changed
A=1, B=2 C=3 and D=4. (0, N/A and blank can be grouped into unscored)
Rate | Rate |
1 | A |
2 | B |
3 | C |
4 | D |
0 | Unscored |
N/A | Unscored |
Hi Srikanth,
simple example:
Rate:
LOAD * INLINE [
Rate
1
2
3
4
0
N/A
];
Rate2:
noconcatenate
LOAD
if(Rate='0' or Rate='N/A','Unscored', Rate) AS Rate
Resident Rate;
Drop Table Rate;
you can try it using applymap also
pfa
SAMPLE INPUT | OUTPUT | |||
RATE | PRODUCT | PRODUCT | RATE1 | |
1 | PROD1 | PROD1 | A | |
2 | PROD2 | PROD2 | B | |
3 | PROD3 | PROD3 | C | |
4 | PROD4 | PROD4 | D | |
0 | PROD5 | PROD5 | UNSCORED | |
4 | PROD6 | PROD6 | D | |
3 | PROD7 | PROD7 | C | |
PROD8 | PROD8 | UNSCORED |
data:
LOAD * INLINE [
RATE, PRODUCT
1, PROD1
2, PROD2
3, PROD3
4, PROD4
0, PROD5
4, PROD6
3, PROD7
, PROD8
];
Temp:
Mapping LOAD * INLINE [
RATE, RATE_BND
1, A
2, B
3, C
4, D
0, UNSCORED
, UNSCORED
];
NEW_DATE:
LOAD
APPLYMAP('Temp',RATE) AS RATE1,
PRODUCT
RESIDENT data;
DROP table data;
Temp:
load * inline [
Rate
1
2
3
4
0
NA
];
load
if(match(Rate,'1'),'A',
if(match(Rate,'2'),'B',
if(match(Rate,'3'),'C',
if(match(Rate,'4'),'D','Unscored')))) as NewRate
Resident Temp;
How about adding the following in your load-script ?
IF(Rate = 1, 'A',
IF(Rate = 2, 'B',
IF(Rate = 3, 'C',
IF(Rate = 4, 'D', Unscored)))) as Newrate;
You can use ApplyMap as below. The advantage of such a method is that the input is normally from a file for the rate/banding. This can be reversed for other systems if required and can also be changed with the changing nature of the business.
Temp_Data:
LOAD * INLINE [
Rate, Value, Amount
1, ABC, 3000
2, XYZ, 2500
3, CFG, 1100
4, GBP, 333
0, VAL, 333
4, ZZZ, 270
3, FFF, 999
, UNK , 7
A, EVE, 99
];
RateMap:
Mapping LOAD * INLINE [
Rate, Banding
1, A
2, B
3, C
4, D
];
DataTable:
LOAD
Value,
ApplyMap('RateMap',Rate,'Unscored') AS Bands,
Rate,
Amount
Resident Temp_Data;
DROP Table Temp_Data;
Giving: