topic Re: Concatenate Tab1 data from 2014,2015,2016 in QlikView

Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 09:53:12 GMT

hi Guru's

i have 3 Excel sheets

1.2014 Sales

2.2015 sales

3.2016 Sales

in future it may add 2017,2018,........

Each excel contains 2 Tabs.

1.Say TAB1

2.Say TAB2

i want to load Automatically these excel data

Concatenate TAB1 Data in each excel Sheet and store as one QVD.

Similarly

i want to load Automatically these excel data

Concatenate TAB2 Data in each excel Sheet and store as one QVD.

How to achieve this.

Any Help would be greately Appreciated

Thanks in Advance

Re: Concatenate Tab1 data from 2014,2015,2016

SreeniJD — Mon, 02 Nov 2015 09:56:03 GMT

Hi Are Babu

Check this thread

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Re: Concatenate Tab1 data from 2014,2015,2016

mayankraoka — Mon, 02 Nov 2015 10:01:30 GMT

Hi,

Please check this:

Storing all tables in Qlikview file to QVD

Regards,

Mayank

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:02:15 GMT

You need to create Two tables Tab1 for all year and Tab2 for all year

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:21:16 GMT

Hi Sreeni,

Thanks for Helpful Answer

But in each Excel i have 2 Tabs.

1.ASIA

2.USA

i want to create 2 QVDS .

means concatenate 2014 ASIA + 2015 ASIA +2016 ASIA and Store as QVD

similarly for USA

1 for ASIA

2 for USA

please find the attached sample data and Application

Thanks !@

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:27:43 GMT

ASIA:

LOAD Year,

Sales

FROM

(ooxml, embedded labels, table is ASIA);

concatenate

LOAD Year,

Sales

FROM

(ooxml, embedded labels, table is ASIA); and so on....

Similar for USA....

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:30:49 GMT

hi Balraj ,

you are right

But how to automate and store as QVD

Thanks

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:31:40 GMT

For further increasing in excel sheet like as you said you may get 2017,2018....

Create a loop for all with like as you shared if format is like 2015.xlsx,2016.xslx,2017.xslx

then you can write like this:

ASIA:

LOAD Year,

Sales

FROM

---------this astrik sign for last deegit

(ooxml, embedded labels, table is ASIA);

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:32:38 GMT

In future the EXCEL Sheets may added

so we have to automate the load

Re: Concatenate Tab1 data from 2014,2015,2016

SreeniJD — Mon, 02 Nov 2015 10:42:46 GMT

The best practice is have all your source files in one folder and mention the same path in your script to all the available excel sheets and you can also name the tables as a source excel file. Storing in to QVDs is just an additional step to your script..

LOAD *

FROM

[Path\*.xlsx]

STORE into .....

Sreeni

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 10:43:11 GMT

Two simple things you need to do:

1) Make sure all are at one common place

2) Naming of Excel file should be common

like 2015.xslx, 2016.xslx,2017.xslx etc

Then use following code for Asia:

ASIA:

LOAD Year,

Sales

FROM

---------this astrik sign for last digit

(ooxml, embedded labels, table is ASIA);

QV will automatically fetch all the Asia data from multiple Excel files...

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 12:41:56 GMT

hi balraj,

Your approach is gud

But My excel names are 2014 sales Report,2015 sales report

Tab Names are 2014 Asia sales,2015 Asia Sales

like above

how to use wild card search in Table name and sheet name

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 12:45:38 GMT

This should work in your case...

ASIA:

LOAD Year,

Sales

FROM

---------this astrik sign for last digit

(ooxml, embedded labels, table is [2014 Asia sales]);

any challenge you are facing?

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 12:49:09 GMT

(ooxml, embedded labels, table is [2014 Asia sales]);

above statement inplace of 2014 i have to give Wildsearch means *

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 12:53:44 GMT

[2014 Asia sales] - is name of Tab for Asia sales in your [2014 Sales Report] excel.

201* means any file started with 201 it should fetch.

Make sure your tab name should be same in each and every excel.

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 12:55:46 GMT

Hi ,

But they want to keep Year before each tab

thats the problem

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 12:58:43 GMT

Could you share their two excel sheets of any two years?

Let me finish it.

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 13:10:17 GMT

these are the actual excels

sheet name is 2014 Audit Stats Tracking,

2015 Audit Stats Tracking

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 13:21:43 GMT

Which tab of both for Asia is common??

Re: Concatenate Tab1 data from 2014,2015,2016

Anonymous — Mon, 02 Nov 2015 13:24:04 GMT

ASIA and USA i mentioned for example

here

2014 Audit Stats Tracking,

2015 Audit Stats Tracking

tabs is common in two excels