topic Re: Load 3 & 4 columns from excelund in QlikView

Load 3 & 4 columns from excelund

Anonymous — Thu, 06 Apr 2017 20:58:39 GMT

Hi All,

I have a source like 2 excel sheet,which is Sheet_3 and Sheet_4.Refer the below source screenshot.

Sheet_3.xlsx:

Col1	Col2	Col3
a	test	1
b	test2	2
c	test4	3
d	test5	4

Sheet_4.xlsx:

Col1	Col2	Col3	Col4
e	T1	6
f	T2	7	pppp
g	T3	8	ooo
h	T4	3	kkk

When I try to load the both the sheet like Sheet_*.xlsx

Script:

Test1:

LOAD Col1,

Col2,

Col3,

Col4

from

(ooxml, embedded labels, table is Sheet1);

That it throws following error. Can any 1 assist me how ican resolve this fix.

Regards,

Shakila D

Re: Load 3 & 4 columns from excelund

hector_munoz — Thu, 06 Apr 2017 21:02:00 GMT

Hi Shakila,

Try the following:

Test1:

LOAD @1 AS Col1,

@2 AS Col2,

@3 AS Col3,

@4 AS Col4

FROM

(ooxml, no labels, table is Sheet1, filters(

Remove(Row, Pos(Top, 1))

));

Regards,

Re: Load 3 & 4 columns from excelund

Anonymous — Thu, 06 Apr 2017 21:02:34 GMT

If i put Load * means my data set will get affected.Its not coming properly.

Re: Load 3 & 4 columns from excelund

Anonymous — Thu, 06 Apr 2017 21:21:37 GMT

Same error came Héctor.

Can you please explain what is the purpose of below the line.

(ooxml, no labels, table is Sheet1, filters(Remove(Row, Pos(Top, 1))));

Re: Load 3 & 4 columns from excelund

vishsaggi — Thu, 06 Apr 2017 21:28:46 GMT

Look here? It might help.

Load different columns from multiple excel files?

Or Try adding just column name Col4 in your first table and load again.

Re: Load 3 & 4 columns from excelund

Anonymous — Thu, 06 Apr 2017 22:09:31 GMT

Hi Vishwarath, The attached URL contains the cross table conceptscontainsI don't want to load the 2 excel sheet with the cross table concept. I need to load the both sheets with single load. My Expected output is: Col1 Col2 Col3 Col4 a test 1 b test2 2 c test4 3 d test5 4 e T1 6 f T2 7 pppp g T3 8 ooo h T4 3 kkk If any other possibilities to load the both sheets with single load. Regards, Shakila D

Re: Load 3 & 4 columns from excelund

Anil_Babu_Samineni — Fri, 07 Apr 2017 01:42:28 GMT

Here, When you try to use * / All. Structure and columns should be same. First sheet you have 3 columns and 2nd sheet you have 4 columns and you used 4 columns with * / All. Here, Qlikview and any other BI Toll won't capable the capture of not in listed field names. Would you re-check to remove 4th column on 2nd sheet then try. And Sheet name should be same for each sheet in excel

Re: Load 3 & 4 columns from excelund

Anonymous — Mon, 10 Apr 2017 17:47:42 GMT

No, I don't want to put * forthis case.Can you please share the alternative options.

When i load the CSV files with following code its got executed, can any 1 tell the reason why it's executed for CSV files not for .xlsx files.

Table1:

LOAD RowNo() as [File Row],

Date(Date#(Textbetween(FileName(), 'tier3-excel_', '.csv'), 'MMDDYYYYTT')) as [File Date],

num(Date#(Textbetween(FileName(), 'tier3-excel_', '.csv'), 'MMDDYYYYTT')) as [Date Number],

@1 as Position,

subfield(@2,'.',1) as [AMI TGB ID],

subfield(@2,'.',2) as [TGB Name],

SubField(@1, '-',1) as [Category ID],

SubField(@1, '-',2) as Category,

if(len(trim(@4))>0, @2 & @3, @2) as [Description],

if(len(trim(@4))>0, @4, @3) as Counts

FROM

(txt, codepageis 1252, no labels, delimiter is ',', msq);