Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have 3 excel sheets in which i have a large chunk of data. The data is represented by their codes. For the first 2 sheets, i have all those codes which i want for my report but in the third sheet there are many unwanted codes which i don't want. Please suggest me a way, so that i can get only those codes into my script which are there in the first 2 sheets. Pls see the below example for your refence-
Excel Sheet 1 | Excel Sheet 2 | Excel Sheet 3 |
---|---|---|
610110 | 610110 | 502312 |
622122 | 622122 | 610110 |
640641 | 640641 | 509876 |
633622 | 633622 | 640641 |
633650 | 633650 | 633622 |
I want only those codes which are there in the first two sheets.
Thanks
Anirban
Anirban,
You can use "where exists" condition to filter data. Example is below:
ExistingCodes:
Load Codes FROM [Excel Data File.xlsx] (ooxml, embedded labels, table is Sheet1);
Concatenate
Load Codes FROM [Excel Data File.xlsx] (ooxml, embedded labels, table is Sheet2);
LoadOnlyExistingCodes:
Load * FROM [Excel Data File.xlsx] (ooxml, embedded labels, table is Sheet3)
Where Exists(Codes);
Regards,
Kiran Rokkam.
Hi Kiran,
Many thanks for your quick revert. I just want to give you a detailed explaination of my situtation . These three sheets are for different region ie US, Europe and Asia. Neither the headers , nor the name of the excel sheets are same, as i am downloading this raw data from 3 diffrent website.And i'm loading it under three different tabs in the qlikview. In the first sheet the code is called HTS6, in the second one its HTS_NUM and in the third its HTS.
Is there anyway, where i can share those 3 excel sheets over here so that you guys can have a look? I dont see the insert file option. I see only inserting image, video and link.
Hi
Click the 'Use advanced editor' option (to the top right of the reply window). The upload option is at the bottom of the advanced editor page
Regards
Jonathan
Attached are the three files from which i want to load data for each region ie US, Europe and Japan. I want to fix the Japan data in line with the other two regions.
Any updates fellas???