Hi Experts!
I have 2 tables that look like this...
data_: //this table contains fees given by customers from various companies
LOAD * Inline
[ customer,company, fee1,fee2,fee3
x,P, 12,4,7
y,P, 14,4,9
z,Q, 0 ,8,0
s,R,9,8,6] ;
main: // this table consists of fees we need to show in the report for each company
LOAD * inline
[company, feeType
P,fee1,
P,fee3
Q,fee2
R,fee3]
I want to select the fee value wherever the FeeType is mentioned in table main, ie, the resulting table should look like this...
resultant:
Load * Inline
[company,customer, feeType
P,x,fee1,12
P,x,fee3,7
P,y,fee1,14
P,y,fee2,9
.....
.....
Q,z,fee3,8]
how can I achieve this in the script?
As of now, I'm using a large if else condition that goes like
if (company='P' and feeType='fee1' then fee1,
if(company='P' and feeType='fee3' then fee3....
and adding Company and Customer as dimensions
Which I'm sure is not the efficient way to do it
