topic Re: Loading uniform codes from separate excel sheets in QlikView

Loading uniform codes from separate excel sheets

Thu, 25 Oct 2012 05:36:31 GMT

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

Re: Loading uniform codes from separate excel sheets

Thu, 25 Oct 2012 05:54:37 GMT

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.

Re: Loading uniform codes from separate excel sheets

Thu, 25 Oct 2012 06:07:07 GMT

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.

Re: Loading uniform codes from separate excel sheets

Thu, 25 Oct 2012 06:11:24 GMT

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.

Re: Loading uniform codes from separate excel sheets

jonathandienst — Thu, 25 Oct 2012 06:17:05 GMT

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

Re: Loading uniform codes from separate excel sheets

Thu, 25 Oct 2012 06:30:15 GMT

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.

Re: Loading uniform codes from separate excel sheets

Fri, 26 Oct 2012 10:32:34 GMT

Any updates fellas???