topic Re: Load specific Excel Cells in QlikView

Load specific Excel Cells

Tue, 10 Apr 2012 11:08:40 GMT

Hello Guys,

is it possible to load not a whole excel file, but specific cells out of the excel file?

For example, if i only want QlikView to load Cells from B2 to B5.

Anyone who has an idea?

Thanks in advance!

Load specific Excel Cells

Tue, 10 Apr 2012 11:47:58 GMT

Hi,

In the load statement of script part, you can remove the unwanted fields and reload the application.

Regards,

Anitha

Load specific Excel Cells

Tue, 10 Apr 2012 12:06:05 GMT

Thanks for the fast reply.

Is there a possibility to load the cells within the first load, and without a reload of the qlikview file?

These specific Excel File i want to load is just one part of a big script and unfortunately it is not possible to run a partially reload.

Re: Load specific Excel Cells

Miguel_Angel_Baeyens — Tue, 10 Apr 2012 12:16:16 GMT

Hi,

Assuming you need to reload the file either completely or partially, you can use a piece of script like the following:

B2ToBB9:
LOAD B, 
FROM
testDataRowsCols.xlsx
(ooxml, no labels, table is Sheet1)
WHERE RecNo() > 1 AND RecNo() < 10;

"B" is the name QlikView uses when there is no label specified or embedded (see the "no labels" tag in the loading parameters of the excel file). Using the function RecNo() you can tell QlikView which rows to load from that excel file, in this case, from row #2 to row #9.

Hope that helps.

Miguel

Load specific Excel Cells

Tue, 10 Apr 2012 12:44:17 GMT

It works. Thanks 🙂

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 08:28:28 GMT

i Miguel this is a very old post i see but when i tried this script it doesn`t work for me i am on QV 11

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 08:43:13 GMT

Hi Farai,

Try:

Load excel file data normally first and modify script accordingly.

Let me know any issues

regards

Neetha

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 08:49:43 GMT

Hi Thanx let me send u sample data of the spreadsheet

Re: Load specific Excel Cells

robert_mika — Fri, 27 Feb 2015 08:51:14 GMT

Please see attached

Script:

Directory;

LOAD Month

FROM

moving.xlsx

(ooxml, embedded labels, table is Sheet1)

WHERE RecNo() > 1 AND RecNo() < 5;

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 08:55:18 GMT

Please look at attached Sample Data

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 08:58:28 GMT

Please attached sample data

i want to load first A1:D14 as a Table

then the row 16 as Campaign

then the data from 17 as Data relevant to a campaign as the Spreadsheet illustrates

thank you

Re: Load specific Excel Cells

robert_mika — Fri, 27 Feb 2015 09:02:44 GMT

Your data model is quite complicated.

Unless you do not have headers for each column it will take a lot more coding that this.

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 09:09:17 GMT

Hi Farai,

Try:

LOAD Campaign,
F2,
Monthly,
Daily,
Days,
[20],
Campaign1,
[Ave Headcount (90% ATT)],
[Order Target],
[Stretch Target],
[Budget Target],
[Target Headcount (Incl Att)]
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE RecNo() > 2 AND RecNo() < 13;

Hope this is you need,else be more specific with your requirements.

Regards

Neetha

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 09:09:48 GMT

oh i get it i will still try other options

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 09:22:24 GMT

thanx i have managed the first part with you code now i cahe to link up campaign with data from 18

Re: Load specific Excel Cells

robert_mika — Fri, 27 Feb 2015 09:28:37 GMT

If I was you I would change the data model:

Example

Script:

Directory;

LOAD Campaign,

F2,

DAY,

[Actual Headcount],

[Daily Target],

Actual,

[Active Agents],

[Ave/Active Agent],

[Ave/ Actual Headcount],

[Income Protect sales],

[Attachment %],

Variance,

Acc

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Sheet1);

Directory;

LOAD Code,

Campaign,

Monthly,

Daily,

Days,

[20],

Campaign1,

[Ave Headcount (90% ATT)],

[Order Target],

[Stretch Target],

[Budget Target],

[Target Headcount (Incl Att)]

FROM

SampleData.xlsx

(ooxml, embedded labels, table is Sheet2);

This still need some clean up but you get the idea

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 09:57:22 GMT

Hi Farai,

See below:

Table:
LOAD Campaign,
F2,
Monthly,
Daily,
Days,
[20],
Campaign1,
[Ave Headcount (90% ATT)],
[Order Target],
[Stretch Target],
[Budget Target],
[Target Headcount (Incl Att)]
FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1)
WHERE RecNo() > 2 AND RecNo() < 13;

Table1:
LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Attachment %],
Variance,
Acc,
'VIVA Ignite Funeral' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate
LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Attachment %],
Variance,
Acc,
'VIA Bridge Banking' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Debt Doctor],
Halo,
Luno,
Variance,
Acc,
'Funeral Approved Live (MS Admin)' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Debt Doctor],
Halo,
Luno,
Variance,
Acc,
'Funeral Approved Live (EM)' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Debt Doctor],
Halo,
Luno,
Variance,
Acc,
'Pre to Post (MS)' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Debt Doctor],
Halo,
Luno,
Variance,
Acc,
'Pre to Post (EM)' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual6,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
[Income Protect sales],
[Attachment %],
Variance,
Acc,
'VL Funeral (EM)' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
GAP,
[Disabilty Cover],
Variance,
Acc,
'Viva Money Shop' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
Kintel,
[Attachment %],
Variance,
Acc,
'Insurance VAS EM' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Concatenate

LOAD July,
DAY,
[Actual Headcount],
[Daily Target],
Actual,
[Active Agents],
[Ave/Active Agent],
[Ave/ Actual Headcount],
Kintel1,
[Attachment %],
Variance,
Acc,
'Insurance VAS MS' as Status
FROM

(ooxml, embedded labels, header is 16 lines, table is Sheet1);

Regards

Neetha

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 10:00:45 GMT

Hi Neetha,

i am actually doing the same and it kind of worked i am also trying to see if any transformation wont get the desired output

thanx for your time

Re: Load specific Excel Cells

Anonymous — Fri, 27 Feb 2015 10:36:24 GMT

Hi farai,

with this load,rows will increase multiplied by campaigns.

Regards

Neetha