Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Not applicable

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

5 Replies
Partner - Champion

Hi Srikanth,

simple example:

Rate:

Rate
1
2
3
4
0
N/A
]
;

Rate2:

noconcatenate

if(Rate='0' or Rate='N/A','Unscored', Rate) AS Rate
Resident Rate;

Drop Table Rate;

Creator

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:

RATE, PRODUCT
1, PROD1
2, PROD2
3, PROD3
4, PROD4
0, PROD5
4, PROD6
3, PROD7
, PROD8
];

Temp:
RATE, RATE_BND
1, A
2, B
3, C
4, D
0, UNSCORED
, UNSCORED
];

NEW_DATE:
APPLYMAP('Temp',RATE) AS RATE1,
PRODUCT
RESIDENT data;

DROP table data;

Anonymous
Not applicable
Author

Temp:

Rate
1
2
3
4
0
NA
]
;

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;

Specialist

IF(Rate = 1, 'A',

IF(Rate = 2, 'B',

IF(Rate = 3, 'C',

IF(Rate = 4, 'D', Unscored)))) as Newrate;

Specialist

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:

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:
Rate, Banding
1, A
2, B
3, C
4, D
]
;

DataTable: