topic Re: load data from 2 excel sheets in QlikView

load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 14:19:40 GMT

hi ,

i want to load data from 2 excel sheets names below.

1.2014 US REMS Audit Workbook DOL

2.2015 US REMS Audit Workbook DOL

Tab Names:

2014 Audit stats Tracking

2015 Audit stats Tracking

i want to combine data as a single Table with automation(Not Manual Concatenation)

OUTPUT Table contains 2014,2015 Audit stats Tracking Data

Thanks in advance

Re: load data from 2 excel sheets

sunny_talwar — Mon, 02 Nov 2015 14:23:40 GMT

If the data fields are the same in the two excel files, then you can try this:

Table:

LOAD *

FROM *US REMS Audit Workbook DOL.xlsx;

Update: Sheet names need to be the same though

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 14:26:56 GMT

hi Sunny,

thanks for your time

fields are same so we can load with 201* like wild card search

But Tab names are Different

Tab Names:

2014 Audit stats Tracking

2015 Audit stats Tracking

here iam unable to use wild card;

Script:

FROM

(ooxml, embedded labels, table is [*Audit Stats Tracking]);

here 201* is working fine but in last line 2014,2015 Audit Stats Tracking i have to use wildcard

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 14:35:44 GMT

you can try with a small Loop

LET vTab = ' Audit stats Tracking';
LET vYear='2014'
for i=1 to 2

let vTest = vYear & vTab;

LOAD *
FROM
[...TEST.xlsx]
(ooxml, embedded labels, table is $(vTest));
Let vYear='2015'
next;

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 14:39:00 GMT

Thanks for your time

in future it may add 2016,2017,2018

is it works for future purpose?

Thanks

error: while reloading

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 16:11:55 GMT

it will work for future years as well

you only Need to Change within the Loop

Let vYear=vYear+1

and you Need to adapt the loopcounter

for i=1 to x

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 18:37:50 GMT

hi Rudolf,

can you please post sample application with my data.

i could not solve the issue

Thanks in advance;

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 19:12:39 GMT

see attached files

if you have further questions, come back to me

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 19:19:28 GMT

no attachment

please attach sample file

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 19:20:11 GMT

here are the files

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 19:51:40 GMT

Hi Rudolf,

thanks for your valueable time

its working fine with your data set

but its throwing error and getting hang with my actual data set

here is the error screen shot

thanks

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 20:38:51 GMT

Hello,

I misunderstood (i am not able to load your larger Excel file).

YOu donot Need the inner Loop, I just changed it (but could be improved more)

it works for the 2014 Excel. please try with both files and let me know

LET vExcelSheet = ' US REMS Audit Workbook DOL.xlsx';
LET vTab = ' Audit stats Tracking';

LET vExcelYear='2014';

for i=1 to 2 // go through all excel sheets

let vExcel = vExcelYear & vExcelSheet;
Let vTabYear = '2014';

//for n=1 to 2 // go through all tabs

let vExcelTab = '[' & vTabYear & vTab & ']';
DATALOAD:
LOAD *
FROM

(ooxml, embedded labels, table is $(vExcelTab));
Let vTabYear=vTabYear+1;
//next n;
Let vExcelYear=vExcelYear+1;
next i;

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 20:52:11 GMT

hi its working for 2014 data.but it should take 2015 as well

its stopping upto 2014 only

how to add 2015 audit stats tracking data to 2014

thanks

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 21:02:32 GMT

can you provide a smaller Excel sheet of 2015? I have a bad Connection right now

I could try with both of your Excel sheets

Re: load data from 2 excel sheets

Anonymous — Mon, 02 Nov 2015 21:15:50 GMT

Hi Are.

I hope this helps, be careful with the order of columns.

Re: load data from 2 excel sheets

tamilarasu — Tue, 03 Nov 2015 02:55:05 GMT

Hi,

I could find a column name mismatch in both the sheets. I have changed the name to Date Certificate Issued, so that we can do concatenation.

You can change the path in front end input box.

sub ScanFolder(Root)

for each FileExtension in 'xlsx'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

Let vYear = Left( subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1),4);

Let vSheetName = vYear & ' Audit stats Tracking';

Data:

LOAD [Audit Date],

[DEA or NABP Number],

[Pharmacy ID],

[Pharmacy Name],

Address,

City,

State,

PharmacyTYpe,

LeadAuditor,

AuditType,

AccountManagerName,

MAAccountDirector,

[Product Audited],

[Overall Audit Result],

[Observation Rating],

[Observation Yes or No],

[Repeat Observations],

[Tier 1],

[Tier 2],

[Number of Counselors],

[Date Audit Report Issued],

[Date Response Received],

[Date Certificate Issued],

[Effectiveness Check],

Comments,

subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName

FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');

next FoundFile

next FileExtension

end sub

Call ScanFolder('$(vFilePath)') ;

Note: If your column names are same, you can use Load * in above script. Also it can work for future years of data .

Let me know.

Re: load data from 2 excel sheets

Anonymous — Tue, 03 Nov 2015 04:58:13 GMT

Hi Nagaraju,

iam getting below error

please find my path in application variable

please find the error attached

thanks for your valuable time

Re: load data from 2 excel sheets

tamilarasu — Tue, 03 Nov 2015 05:05:15 GMT

Can you check the excel which you are trying to load (2014.xlsx). Above error shows that, there is no Audit Date column in your excel.

Re: load data from 2 excel sheets

Anonymous — Tue, 03 Nov 2015 06:58:24 GMT

hi Nagraj,

thanks for your Time .its working fine

but

in same folder i have excels which is not related to this load.

code seaching in those excel sheets and throwing error Audit date Field not available.

it should Consider only "2014 US REMS Audit Workbook DOL","2015 US REMS Audit Workbook DOL","2016 US REMS Audit Workbook DOL",2017 US REMS Audit Workbook DOL ......so on

where i need to apply this condition in script

thanks for valuable time

Are Babu

Re: load data from 2 excel sheets

tamilarasu — Tue, 03 Nov 2015 07:14:04 GMT

Hi Are BaBu,

I just added an extra If statement to check the file name. Check the attachment.

sub ScanFolder(Root)


for each FileExtension in 'xlsx'
   for each FoundFile in filelist( Root & '\*.' & FileExtension)

Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);

if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then

Let vYear = Left(vFileName,4);
Let vSheetName = vYear & ' Audit stats Tracking';

    Data:    
      LOAD [Audit Date],
     [DEA or NABP Number],
     [Pharmacy ID],
     [Pharmacy Name],
     Address,
     City,
     State,
     PharmacyTYpe,
     LeadAuditor,
     AuditType,
     AccountManagerName,
     MAAccountDirector,
     [Product Audited],
     [Overall Audit Result],
     [Observation Rating],
     [Observation Yes or No],
     [Repeat Observations],
     [Tier 1],
     [Tier 2],
     [Number of Counselors],
     [Date Audit Report Issued],
     [Date Response Received],
     [Date Certificate Issued],
     [Effectiveness Check],
     Comments,
     '$(vFileName)' as FileName
FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');
    
   End If   

   next FoundFile
next FileExtension
end sub

Call ScanFolder('$(vFilePath)') ;