Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I am facing data joining issue.Initially my data has two tables as below:
I used applymap for Region and country columns so as to get full names,but still I am getting table as above.I need something which looks like:
Please help me.....
FACT:
LOAD Region,
Country,
City,
SiteID,
AccountNumber,
Model,
Desc,
Amount
FROM
(ooxml, embedded labels, table is Sheet1);
JOIN
LOAD
AccountNumber,
BillingNum,
Sale
FROM
(ooxml, embedded labels, table is Sheet2);
Hi if u are expecting common data plss do inner join like
Table 1
Inner join
Table 2..
But do note that the join I'd should be Site ID and other than that make sure that u alias rest of the other column .
If table 1 is main table then use left join in btn..Don't keep column names same in both tables same..And comment out unwanted columns like u don't want region n country frm table 2 then comment them out.
Sachin
Do you have an excel sheet data that depicts what you have in that image to work on it ? Please attach if possible?
Hi Sachin
I need fields from both tables,otherwise I am not getting respective SALE value if I don't use fields in second table,here AS and ASIA are of same data but are differing in names,
Then atleast rename them and join else ur joining 2 tables with key which includes all multiple columns..
It will lead to wrong result..U join based on siteId only..Rest fields in tble 2 rename as per ur wish..
Check the attached qvw.
I hope you find it helpfull
You don't need applymap here!Just Left join both the tables......you already have full country,region and city names in table1 so use the same
Table1:
Region,
country,
city,
AccountNumber,
Model,
desc,
Amount,
SiteID,
SiteID&'_'&AccountNumber as KEY
FromTable_1_SRC;
left join
Table2:
BillingNumber,
Sale,
SiteID&'_'&AccountNumber as KEY
FromTable_2_SRC;
Hi Sachin/Vineeth
Got upto an extent
but my data have two rows in first table and second table have more rows as shown
but my data is showing like
hope u understood my problem now.
Hi,
As per initial table images,
Please try using key I.e combination of site I'd and acct no..
Please read this thread for understanding
Hi
I used account number as key,as I can't use combination of both.But,data which have more columns with same Account number are not showing values.It should be like:
I don't understand why these values are missing if there are more rows