Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ];
Hi Chandan
Please find the attached file
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.
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.
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;
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
What about the actual Temp2 table? Can you tell me what Temp2 table contains
Temp2 table contains missing currency rate
that is
Hash128(Date,Currency) as FXKEY, Rate
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;