Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

gidon500
Contributor II

Load Multi Columns from excel file

hi

I have a request to load multi columns from excel file

I do not know how many rows and columns I have

it is very simple table  .

I get  multi columns and need to create 2 columns table

enclosed please find excel file  with one sheet

on the left  is the input format

and in the right is the output  format

thanks

gidon

1 Solution

Accepted Solutions

Re: Load Multi Columns from excel file

try below this will load all the columns dynamically using * prefix

Data:

CrossTable(Col,Balcode)

LOAD Description,

    *

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

NoConcatenate

LOAD *

Resident Data

where not Exists(Description,Balcode);

DROP Table Data;

DROP Field Col;



5 Replies

Re: Load Multi Columns from excel file

Data:

CrossTable(Col,Balcode)

LOAD Description,

    BalCode,

    F3,

    F4,

    F5

FROM

[loadmulticolmns.xlsx]

(ooxml, embedded labels, header is 1 lines, table is DATA);

DROP Field Col;


gidon500
Contributor II

Re: Load Multi Columns from excel file

Hi Kush

It works great  ,

but my problem is that I do not know how many columns I am going to have

it can be 3 or 7

with new rows that will be added by the CFO

can we fix it

Gidon

Re: Load Multi Columns from excel file

try below this will load all the columns dynamically using * prefix

Data:

CrossTable(Col,Balcode)

LOAD Description,

    *

FROM

(ooxml, embedded labels, table is Sheet1);

Final:

NoConcatenate

LOAD *

Resident Data

where not Exists(Description,Balcode);

DROP Table Data;

DROP Field Col;



gidon500
Contributor II

Re: Load Multi Columns from excel file

THANKS

HAPPY NEW YEAR

GIDON

Re: Load Multi Columns from excel file

Welcome & HAPPY NEW YEAR TO YOU AS WELL