topic Re: mapping missing record in QlikView

mapping missing record

Anonymous — Thu, 27 Aug 2015 09:55:31 GMT

Hi,

I am new to qlikview and not sure how to resolve the problem. My problem is

I have one table structure like

Temp1:

Id,Name, salary

1, A, 10000

2,B

3,C,2000

Temp2:

Id,Name, salary

2,B,500

I want a final table like

Temp_Final:

Id,Name, salary

1, A, 10000

2,B,500

3,C,2000

how I can join bothe the table to get desired output

Re: mapping missing record

Kushal_Chawda — Thu, 27 Aug 2015 10:03:26 GMT

try,

Map:

mapping load Id&Name as Key,salary Inline [

Id,Name, salary

2,B,500 ];

Temp1:

LOAD *, ApplyMap('Map',Id&Name,salary) as Sal_New Inline [

Id,Name, salary

1, A, 10000

2,B

3,C,2000 ];

Re: mapping missing record

Thu, 27 Aug 2015 10:08:08 GMT

Hi Chandan

Please find the attached file

Re: mapping missing record

tresB — Thu, 27 Aug 2015 10:11:09 GMT

Mapping table comprises of two fields. Rather try like:

Map:

mapping load Id&Name as Key,salary Inline [

Id, salary

2, 500 ];

Output:

LOAD *, ApplyMap('Map',Id,salary) as Sal_New Inline [

Id,Name, salary

1, A, 10000

2,B

3,C,2000 ];

Assuming Id is key field.

Re: mapping missing record

Anonymous — Thu, 27 Aug 2015 11:09:49 GMT

Hi kush,

thanks for your reply, My scenario is, I already have temp1 in my system and I am loadingTemp2 later. the salary in Temp1 is getting converted in INR after dividing different curr by INR rate. now the problem is for some of the currency I don't have rate and that is why I am loading Temp2 where I have the rate for missing currency.

also 2nd problem in praposed solution is, it is adding new column sal_new which in my case I can not use as salary is getting used every where in my application. so I have to replace the null value in Temp1 with Temp2 value.

Re: mapping missing record

Kushal_Chawda — Thu, 27 Aug 2015 11:30:52 GMT

Please provide the sample data with actual scenario with expected output. Perhaps you can map the currency from Temp2 to Temp1 with Same Key and then do your conversion in Temp1

Temp2:

mapping load Id&Name as Key,Rate

From Temp2

Temp1:

LOAD *, ApplyMap('Temp2',Id&Name,Rate) as Rate_New

From Temp1;

Final:

noconcatenate

load *,

Cuur*Rate_New as Field

Resident Temp1;

drop table Temp1;

Re: mapping missing record

Anonymous — Thu, 27 Aug 2015 11:49:14 GMT

Below is my Temp1 table creation script

Temp1:
noconcatenate
LOAD *,
Hash128(Date,Currency) as FXKEY
resident Temp
where Date <> null();

left join
LOAD
FXKEY,
Rate,
Resident Curr_Rate;

left join
LOAD
*,
Round(Risk.3M/Rate, .00000001) as RiskINR.3M,
Round(Risk.1Y/Rate, .00000001) as RiskINR.1Y,
Resident Temp1;

now from above table 1st I am checking for which record in temp1 I am not having RiskINR.3M buz of missing currency Rate in Curr_Rate table.

so only for those record I have to do calculation again and fill the table back.

that is why I have given below scenario

Temp1:

Id,Name, salary

1, A, 10000

2,B

3,C,2000

Temp2:

Id,Name, salary

2,B,500

I want a final table like

Temp_Final:

Id,Name, salary

1, A, 10000

2,B,500

3,C,2000

Re: mapping missing record

Kushal_Chawda — Thu, 27 Aug 2015 12:09:18 GMT

What about the actual Temp2 table? Can you tell me what Temp2 table contains

Re: mapping missing record

Anonymous — Thu, 27 Aug 2015 12:20:52 GMT

Temp2 table contains missing currency rate

that is

Hash128(Date,Currency) as FXKEY, Rate

Re: mapping missing record

Kushal_Chawda — Thu, 27 Aug 2015 12:45:08 GMT

Temp1:

noconcatenate

LOAD *,

Hash128(Date,Currency) as FXKEY

resident Temp

where Date <> null();

left join

LOAD

FXKEY,

Rate,

Resident Curr_Rate;

left join

LOAD

Hash128(Date,Currency) as FXKEY, Rate as Rate1

From Temp2

New:

noconcatenate

load *,

if(len(trim(Rate))=0,Rate1,Rate) as RateFinal

resident Temp1;

drop table Temp1;

Final:

noconcatenate
LOAD
*,
Round(Risk.3M/RateFinal, .00000001) as RiskINR.3M,
Round(Risk.1Y/RateFinal, .00000001) as RiskINR.1Y,
Resident New;

drop table New;