topic Re: Reload Excel file with duplicate field name in QlikView

Reload Excel file with duplicate field name

Fri, 06 Feb 2015 01:51:31 GMT

Hi all:

I want to load in data from an excel file.

The excel file is autogenerated overnight from a system. I understand that having two different fields with same name is not the best practice for database. But they are from other department and for now I don't have any control over the field name.

There are two fields both called 'Status' but containing different data.

How can I load both fields into Qlikview?

Thank you very much.

Paco

Re: Reload Excel file with duplicate field name

sunny_talwar — Fri, 06 Feb 2015 02:11:06 GMT

You can force them to be loaded with different names

For example

LOAD Status as Status_DepartmentName1

....

Resident Excel1

LOAD Status as Status_DepartmentName2

....

Resident Excel2

This way you won't have two fields with the same name.

I hope that this is what you are looking for, if not please clarify and some one might have a solution for you.

Best,

Re: Reload Excel file with duplicate field name

Fri, 06 Feb 2015 03:33:37 GMT

Hi S:

Thank you very much for your reply.

I should clarify that I am loading one excel file. And there are two different fields with same name in the same excel file. And the field has nothing to do with 'Departments'. It is just that the data is provided by other departments, not the department I am in.

I try to rename one of the 'Status' field (lets say I rename to 'Status_new') but the result is both 'Status' and 'Status_new' are the same field. So one of the original 'Status' field are ignored.

Hope this would help you understand the data.

Re: Reload Excel file with duplicate field name

PrashantSangle — Fri, 06 Feb 2015 04:15:11 GMT

Hi,

Post your script, before doing modification.

and if possible post your sample excel file.

Regards

Re: Reload Excel file with duplicate field name

avinashelite — Fri, 06 Feb 2015 04:30:42 GMT

Hi,

Please post your app and excel file , its hard to guess how is the data and fields

Re: Reload Excel file with duplicate field name

rwunderlich — Fri, 06 Feb 2015 04:43:59 GMT

The excel load has a bit of magic. If you use the "embedded labels" option, the first column labeled "Status" will be known as "Status". The second column labeled "Status" will be known as "Status1". So a generated LOAD statement, or one you code yourself will look like:

LOAD

Status,

Status1

From xxx (biff, embedded labels,,,)

;

Even though both columns have the heading "Status".

-Rob

Re: Reload Excel file with duplicate field name

Fri, 06 Feb 2015 05:03:03 GMT

Hi Rob:

Thanks for your advice.

But my autogenerated script didn't automatically change the second field to 'Status1'.

And when I change it manually, it said it didn't recognise the field.

Here is an example of my excel file:

Re: Reload Excel file with duplicate field name

manojkulkarni — Fri, 06 Feb 2015 05:14:15 GMT

Check below script.

LOAD Status,

[ID Number],

[Full Name],

[Record Type],

Status AS Status1,

[Hours Code],

[Hours Description],

[A/D Code],

[A/D Code Description],

[Date From],

[Date To],

[Return To Work],

Hours

FROM

[Sample File.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Reload Excel file with duplicate field name

Fri, 06 Feb 2015 05:16:37 GMT

Hi:

Have you tried your code?

It doesn't work for me...

Thanks

Re: Reload Excel file with duplicate field name

PrashantSangle — Fri, 06 Feb 2015 05:28:57 GMT

Hi,

There is no direct way to acheive this.

You can try like with explicit labels

try below code

LOAD

@1 as Status,
@2 as [ID Number],
@3 as [Full Name],
@4 as [Record Type],
@5 as Status1,
@6 as [Hours Code],
@7 as [Hours Description],
@8 as [A/D Code],
@9 as [A/D Code Description],
@10 as [Date From],
@11 as [Date To],
@12 as [Return To Work],
@13 as Hours
FROM

(txt, codepage is 1252, explicit labels, delimiter is ',', msq, filters(
Remove(Row, Pos(Top, 1))
));

REgards

Re: Reload Excel file with duplicate field name

Fri, 06 Feb 2015 05:30:55 GMT

Ah...

I see what you mean. I think this could work.

Thank you very much.

Re: Reload Excel file with duplicate field name

jmmayoral3 — Fri, 06 Feb 2015 08:57:47 GMT

Hi.

Pay Attention that Max Dreammer´s script loads the headers in the first record as they where data.

Use theese script to load an auxiliar table (for example: excel_aux)

and just after load the true table:

Name_Table:

LOAD *

RESIDENT excel_aux

WHERE Status <> 'Status';

Drop table excel_aux;

Re: Reload Excel file with duplicate field name

PrashantSangle — Fri, 06 Feb 2015 09:41:35 GMT

Hi,

If you see my script in that initially I load all headers as data with explicit labels but you can see during transformation step. I am removing 1st row so that header value will not be loaded as data.

Regards

Re: Reload Excel file with duplicate field name

jmmayoral3 — Fri, 06 Feb 2015 10:12:00 GMT

You're absolutely right. I didn´tt see it.

Sorry.

Re: Reload Excel file with duplicate field name

Anonymous — Fri, 06 Feb 2015 12:19:46 GMT

Hi,

Please can you upload sample data.

Regards

Neetha

Re: Reload Excel file with duplicate field name

rwunderlich — Fri, 06 Feb 2015 18:26:49 GMT

Your original question asked about "excel file". I assumed you meant .xls or .xlsx file -- which is where the auto renaming of columns works. It would not work with a csv file.

-Rob