topic Re: Mapping to Dimension while loading it self in QlikView

Mapping to Dimension while loading it self

Tue, 03 Mar 2015 11:49:42 GMT

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

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

Re: Mapping to Dimension while loading it self

awhitfield — Tue, 03 Mar 2015 12:08:41 GMT

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;

Re: Mapping to Dimension while loading it self

Vidit — Tue, 03 Mar 2015 12:17:13 GMT

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;

Re: Mapping to Dimension while loading it self

Anonymous — Tue, 03 Mar 2015 12:22:43 GMT

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;

Re: Mapping to Dimension while loading it self

puttemans — Tue, 03 Mar 2015 12:29:26 GMT

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;

Re: Mapping to Dimension while loading it self

Roop — Tue, 03 Mar 2015 12:44:51 GMT

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: