Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Reload Excel file with duplicate field name

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

Tags (1)
15 Replies

Re: Reload Excel file with duplicate field name

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,

S

Not applicable

Re: Reload Excel file with duplicate field name

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

Hi,

Post your script, before doing modification.

and if possible post your sample excel file.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Reload Excel file with duplicate field name

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

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

Not applicable

Re: Reload Excel file with duplicate field name

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:

manojkulkarni
Valued Contributor II

Re: Reload Excel file with duplicate field name

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);

Not applicable

Re: Reload Excel file with duplicate field name

Hi:

Have you tried your code?

It doesn't work for me...

Thanks

PC

Re: Reload Excel file with duplicate field name

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

Great dreamer's dreams never fulfilled, they are always transcended.
Community Browser