Load specific Excel Cells

[#1]

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!

[Anonymous]

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

[robert_mika]

Your data model is quite complicated.

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

[Anonymous]

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 

[Anonymous]

oh i get it i will still try other options

[Anonymous]

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

[robert_mika]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi farai,

with this load,rows will increase multiplied by campaigns.

Regards

Neetha