topic Uploading data from different sheet in QlikView

Uploading data from different sheet

Wed, 28 Sep 2011 13:43:16 GMT

HI,

i have an excel with country data and each sheet has different country, in wht way can i upload the data
thanks

Re: Uploading data from different sheet

its_anandrjs — Wed, 28 Sep 2011 13:46:32 GMT

Hi,

If the column name of Country is same like Country what ever it is then when you load this sheets the Qlikview automatically connect thic Country column.

Rgds

Anand

Uploading data from different sheet

Wed, 28 Sep 2011 13:56:35 GMT

thanks for the reply, actually there is not specifie field for country, the country name is only present in the "Tab" Name but the rows and colums are same,

Uploading data from different sheet

Wed, 28 Sep 2011 13:57:30 GMT

Pleae see the attached picture in the previous comment

Uploading data from different sheet

Wed, 28 Sep 2011 15:06:14 GMT

Type it like this in your load script:

for each sheet in 'India', 'Srilanka','Burma'

Salesdata:
Load Customers,Sales
From Salesdata.xlsx (ooxml, embedded labels, table is $(sheet));

next;

Regards, Robert

Re: Uploading data from different sheet

its_anandrjs — Wed, 28 Sep 2011 16:29:22 GMT

Hi,

You have to load some thing like this and make a qvds of the tables and when you load the individual tables and laod with Filebasename( ) and store in with name Country in the load of three qvds

See the attached sample file to get the result

Let me know about this.

HTH

Rgds

Anand

Re: Uploading data from different sheet

Thu, 29 Sep 2011 06:25:40 GMT

hi anand
i have QlikView Personal Edition so i am unable to open ur file, it will be great help if you can provide the script in some other format
thanks

Re: Uploading data from different sheet

Thu, 29 Sep 2011 09:47:16 GMT

Did you try my loadscript above?
Robert

Uploading data from different sheet

Thu, 29 Sep 2011 10:24:24 GMT

Hi Robert
i have tried it bt its not working its Scripting error

Re: Uploading data from different sheet

Thu, 29 Sep 2011 10:36:18 GMT

My script is working on my side in QV9. I'll post the script as a textfile in case this editor is corrupting the script. /Robert

Uploading data from different sheet

Thu, 29 Sep 2011 12:51:20 GMT

Thanks for ur efforts. Now it has worked, but not able to get the country Field, can u suggest if u have any idea that particular sheet data is loaded for each country

Re: Uploading data from different sheet

Thu, 29 Sep 2011 17:26:40 GMT

This script will also give you the "sheet name" as country in the load script.

Re: Uploading data from different sheet

its_anandrjs — Fri, 30 Sep 2011 04:41:57 GMT

Hi,

Sorry for the late response see the script code.

Example below

India:

LOAD ColA

FROM

CountryData.xlsx

(ooxml, embedded labels, table is India);

Japan:

LOAD ColB

FROM

CountryData.xlsx

(ooxml, embedded labels, table is Japan);

China:

LOAD ColC

FROM

CountryData.xlsx

(ooxml, embedded labels, table is China);

Store India into India.qvd;

Store Japan into Japan.qvd;

Store China into China.qvd;

Drop tables India,Japan,China;

LOAD ColA,

filebasename() as Country

FROM

India.qvd

(qvd);

LOAD ColB,

filebasename() as Country

FROM

Japan.qvd

(qvd);

LOAD ColC,

filebasename() as Country

FROM

China.qvd

(qvd);

HTH

Rgds

Anand

Re: Uploading data from different sheet

its_anandrjs — Fri, 30 Sep 2011 05:10:51 GMT

Hi,

Let me know if it is works for you.

Hope this helps you

Rgds

Anand

Uploading data from different sheet

Fri, 30 Sep 2011 06:23:03 GMT

hi robert

still i am not able to get the country field

The script is as below:

for

each sheet in '[Monthly TOTAL]', 'US','Japan', 'Germany', 'UK', 'Canada'

Salesdata:

crossTable

(Months, Data, 1)

Load

[all periods "AS BOOKED"],

[January cum.]

as Jan,

[February cum.]

as Feb,

[March cum.]

as Mar,

[April cum.]

as Apr,

[May cum.]

as May,

[June cum.]

as Jun,

[July cum.]

as Jul,

[August cum.]

as Aug,

[September cum.]

as Sep,

[October cum.]

as Oct,

[November cum.]

as Nov,

[December cum.]

as Dec,

'$(sheet)'

as Country

From

[Source data\country_sales 2011.xlsx] (ooxml, embedded labels, table is $(sheet) );

next;

let me know

Re: Uploading data from different sheet

Fri, 30 Sep 2011 06:36:23 GMT

Finally got it, As i was using cross table to upload the data, the country field was not getting displayed, i have once again written "For" statement separately to get the sheet name as country, below is the script

After running the previous script from wch i have removed country and jst added the below script

for

each sheet in '[Monthly TOTAL]', 'US','Japan', 'Germany', 'UK', 'Canada'

Salesdata1:

load

'$(sheet)' as Country

From

[Source data\country_sales 2011.xlsx] (ooxml, embedded labels, table is $(sheet));

next;

Uploading data from different sheet

Fri, 30 Sep 2011 06:43:49 GMT

Hi Anand thanks for your help reply ur script work fine bt its too long,