Solved: Re: load data from 2 excel sheets - Qlik Community

Anonymous · ‎2015-11-02

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

tamilarasu · ‎2015-11-03

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)') ;

View solution in original post

sunny_talwar · ‎2015-11-02

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

Anonymous · ‎2015-11-02

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

Anonymous · ‎2015-11-02

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;

Anonymous · ‎2015-11-02

Hi

Thanks for your time

in future it may add 2016,2017,2018

is it works for future purpose?

Thanks

error: while reloading

Anonymous · ‎2015-11-02

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

Anonymous · ‎2015-11-02

hi Rudolf,

can you please post sample application with my data.

i could not solve the issue

Thanks in advance;

Anonymous · ‎2015-11-02

see attached files

if you have further questions, come back to me

Anonymous · ‎2015-11-02

no attachment

please attach sample file

Anonymous · ‎2015-11-02

here are the files