topic Re: excel column selection in QlikView

excel column selection

Anonymous — Fri, 24 Aug 2012 17:43:01 GMT

hi its urgent pls Help for me....

there is no Column header in excel...

I need to select column in excel

A B C D E F G H I J K L default column in excel. how to fetch default column wise data in excel

Re: excel column selection

Anonymous — Fri, 24 Aug 2012 18:16:51 GMT

Use the following system to refer to the column

LOAD @1,

@2,

@3,

@4,

@5,

@6,

@7,

@8,

FROM

"your excel file"

Here 1,2,3,4.... refer to the column number of your excel sheet.

Re: excel column selection

Anonymous — Fri, 24 Aug 2012 18:20:55 GMT

how to merge the column

Re: excel column selection

Fri, 24 Aug 2012 20:36:31 GMT

Do you mean you want the data from columns A-L all in one column?

Re: excel column selection

Anonymous — Fri, 24 Aug 2012 20:46:38 GMT

LOAD

@1 & @2 & @3 as Field123,

...

FROM

"your excel file"

Re: excel column selection

Anonymous — Fri, 24 Aug 2012 21:35:00 GMT

my Real requirment is

I Attached Invoice Excel sheet

I need to export another excel format

for example SNo InvoiceNo Date

1 5142 / 11-12 -PI 12/5/2011

Re: excel column selection

sebastiandperei — Fri, 24 Aug 2012 23:03:24 GMT

Yes, it would be better...

I think this invoice cames from a DataBase (SQL, ACCESS, etc..), can you get access to this source?

Re: excel column selection

bgerchikov — Fri, 24 Aug 2012 23:05:09 GMT

Sarath,

Assume you're loading one invoice at the time and Invoice format is the same for all:

Re: excel column selection

Anonymous — Fri, 24 Aug 2012 23:43:23 GMT

great Boris Gerchikov

can U explain little bit more...and qvw file ..it makes more helpful for me i am a beginner in qlikview. also I need discription column,Rates

Re: excel column selection

bgerchikov — Fri, 24 Aug 2012 23:53:21 GMT

Sarath,

you need the data located in 2 columns B and J, correct? To identify S.No. value in B column I used label "S.No." in the same column two rows above:

Where Previous(Previous(trim(B)))='S.No.'

For two other values I used labels located in column "I" at the same row.

In simple words I've selected value in column J based on the value in column I.

Re: excel column selection

Anonymous — Sat, 25 Aug 2012 00:03:49 GMT

how to get merged column that is description column

Re: excel column selection

bgerchikov — Sat, 25 Aug 2012 00:54:32 GMT

Add the following statement (approach is the same):

join LOAD Distinct

E as Description

FROM
invoice.xlsx
(ooxml, no labels, table is [sheet1 (2)])
Where Previous(Previous(trim(E)))='Description'

;

Re: excel column selection

Anonymous — Sat, 25 Aug 2012 05:50:49 GMT

The solution provided will work as long as there is no change in the excel file.

i.e it will fail if there are more than one product or items are there (in that case the data for S.No. 2 and above will not be fetched)

the following will provide a better solution although it is indirect in approach

tab1:

LOAD

B as [S.No.],

E as Description,

recno() as rno

FROM

[invoice.xlsx]

(ooxml, no labels, table is [sheet1 (2)])

;

join load recno() as rec1

FROM

[invoice.xlsx]

(ooxml, no labels, table is [sheet1 (2)])

Where trim(B) = 'S.No.';

join load recno() as rec2

FROM

[invoice.xlsx]

(ooxml, no labels, table is [sheet1 (2)])

Where trim(B) = 'B.E.D. + SAD';

tab2:

LOAD

[S.No.] as sno,

Description as Desc

resident tab1

where rno > rec1

and rno < rec2

;

join load

J as InvoiceNo

FROM

[invoice.xlsx]

(ooxml, no labels, table is [sheet1 (2)])

Where trim(I) = 'No.';

join LOAD

date( J,'MM/DD/YYYY') as Date

FROM

[invoice.xlsx]

(ooxml, no labels, table is [sheet1 (2)])

Where trim(I) = 'Date';