Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kicchu465
Creator
Creator

Loading data from Excel from Specific Column

Hi Experts,

I have a problem in loading the data from Excel.

My requirement is to load from Excel from specific cell no. The Cell no is I19 to so on.

I have attached sample data and the load script I have used but no luck.

Could you please help me in getting this done.

I:

LOAD *

FROM [Sample Data1]

(biff, embedded labels,header is 19 lines, table is Table$)

Thanks,

S k

9 Replies
arvind1494
Specialist
Specialist

I your excel sheet their are no of tables which table do you want to load from cell no 19 ?

kicchu465
Creator
Creator
Author

Hi Arvind,

I need it from Cell no i19.

I want to eliminate Columns A to H and Rows 1 to 18.

Thanks,

S K

arvind1494
Specialist
Specialist

LOAD 

     F9,

     [Cost Center],

     F11,

     [Cost Element],

     F13,

     [WBS Element],

     F15,

     [Internal Order],

     F17,

     [APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000]   

FROM

(biff, embedded labels, header is 8 lines, table is Table$);

Note: in your data for cell no 19 few values are null there for those columns are showing F9,F11,F13 and so on

arvind1494
Specialist
Specialist

This is output in front end as table object:

kicchu465
Creator
Creator
Author

Hi Aravind,

Thanks for your reply.

The excel will be updated month on Month and therefore every month I'll be getting extra column after

[AUG 18 Actuals

in '000]

for example when data gets refresh  this month there will be on more column called

[SEP18 Actuals

in '000]

So the Script should be dynamic to pick those columns as well. Is that achievable?

Thanks,

S k

arvind1494
Specialist
Specialist

Tell me

the dynamic column will be for upcoming months only right?

kicchu465
Creator
Creator
Author

Hi Arvind,

Yes.

The data will be updated till Mar'19 and in then we will receive next file for next financial year.

In the data file the below will static and there wont be any changes in column position.

F9,

     [Cost Center],

     F11,

     [Cost Element],

     F13,

     [WBS Element],

     F15,

     [Internal Order],

     F17

The rest of fields should be picked dynamically.

Thanks,

S k

arvind1494
Specialist
Specialist

let vThisMonth = num(month(Today()));

SET vFixCol =F9,

     [Cost Center],

     F11,

     [Cost Element],

     F13,

     [WBS Element],

     F15,

     [Internal Order],

     F17;

set vApr = [APR 18 Actuals

in '000];

set vMay = [APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000];

set vJun =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000];

set vJul =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000];

set vAug =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000];

set vSep =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000];

set vOct =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000];

set vNov =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000];

set vDec =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000];

set vJan =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000],

[JAN 19 Actuals

in '000];

set vFeb =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000],

[JAN 19 Actuals

in '000],

[FEB 19 Actuals

in '000];

set vMar =[APR 18 Actuals

in '000],

     [MAY 18 Actuals

in '000],

     [JUN 18 Actuals

in '000],

     [JUL 18 Actuals

in '000],

     [AUG 18 Actuals

in '000],

[SEP 18 Actuals

in '000],

[OCT 18 Actuals

in '000],

[NOV 18 Actuals

in '000],

[DEC 18 Actuals

in '000],

[JAN 19 Actuals

in '000],

[FEB 19 Actuals

in '000],

[MAR 19 Actuals

in '000];

TRACE $(vThisMonth);

let vMonthName= 'v'&month(Today());

TRACE $(vMonthName);

//Load statement

LOAD

 

     $(vFixCol),

     $($(vMonthName))

 

FROM

(biff, embedded labels, header is 8 lines, table is Table$);

kicchu465
Creator
Creator
Author

Hi Arvind,

Excellent solution.

I am trying to below,but I am facing an issue.

Table:

CrossTable (Month, Measure,9)


LOAD  


     F9 as [Cost Description],


     [Cost Center],


     F11 as [Cost Center Description],


     [Cost Element],


     F13 as [Cost Element Description],


     [WBS Element],


     F15 as [WBS Element Name],


     [Internal Order],


     F17 as [Internal Order Text]