Mapping to Dimension while loading it self

[#1]

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

[awhitfield]

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;

[Vidit]

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;

[Anonymous]

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;

[puttemans]

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;

[Roop]

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: