Same Field Names

[gerhardl]

Hi,

I'm pulling a large daily extract into Qlikview. The extract is from a delimted text file, but it has two fields with the same name. In my load statement I did the following:

[Address line] as [Address line 1],
[Address line] as [Address line 2],

I thought that it was working fine until I finally wanted to use these fields, and I noticed that the first one (which I thought I renamed to Address Lime 1) now is just a duplication of the second field.

So I now have two separate fields (Address Line 1 and Address Line 2) but they both have the same data, which is from the second field in the text file.

Any advice? I urgently need both fields to be loaded.

Thanks in advance,

Gerhard

[Miguel_Angel_Baeyens]

Hello Gerhard,

If you don't use the labels that already exists in the CSV file, and mark it as a header line instead, you will get something like the following (using the "Table Files" button in the Script Editor window:

File:LOAD @1 AS Name, @2 AS LastName, @3 AS Address1, @4 AS Address2FROMfile.csv(txt, codepage is 1252, no labels, delimiter is ',', msq, header is 1 lines);

Now you can rename @3 and @4 as Address1 and Address2 in the load script making sure you are loading the proper values.

Hope that helps.

[gerhardl]

Hi Miguel,

That will solve the problem - I was hoping there is another way, as I have 150 fields, and I will then have to rename them all. But I'll only have to do it once I suppose, so I better get to it...

Thanks,

G

[gerhardl]

Hi Miguel,

Actually - now that I think about it that would complicate things A LOT for me. I have a long script as it is, with a bunch of changes made (new fields added and date formats, etc.). If I load the table file again, it would take me forever to recreate the script. Is there any other way?

Here is my script:

Directory;
LOAD [Product Name],
[ID number],
[Account no],
[Card no],
DATE(DATE#( [Account open date], 'DD-MMM-YY')) as [Account open date],
month(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open month],
year(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open year],
day(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open day],
[Account status code],
[Charge off Status],
DATE(DATE#( [Account status change date], 'DD-MMM-YY')) as [Account status change date],
[Previous status],
[Store no],
[Block type code1],
[Block type code2],
[Credit limit],
[Account balance],
[OTB (excl. oversell)],
[Total due],
[Total past due],
[Recency Delinquency Status],
[Cycle due status],
[CD0 value],
[CD1 value],
[CD2 value],
[CD3 value],
[CD4 value],
[CD5 value],
[CD6 value],
[CD7 value],
[CD8 value],
[CD9 value],
DATE(DATE#( [Date of last payment], 'DD-MMM-YY')) as [Date of last payment],
month(DATE(DATE#( [Date of last payment], 'DD-MMM-YY'))) as [Month of last payment],
year(DATE(DATE#( [Date of last payment], 'DD-MMM-YY'))) as [Year of last payment],
day(DATE(DATE#( [Date of last payment], 'DD-MMM-YY'))) as [Day of last payment],
[Value of last payment],
[Number of payments in cycle],
[Value of payments in cycle],
DATE(DATE#( [Date of first purchase], 'DD-MMM-YY')) as [Date of first purchase],
[Value of first purchase],
DATE(DATE#( [Date of last purchase], 'DD-MMM-YY')) as [Date of last purchase],
month(DATE(DATE#( [Date of last purchase], 'DD-MMM-YY'))) as [Month of last purchase],
year(DATE(DATE#( [Date of last purchase], 'DD-MMM-YY'))) as [Year of last purchase],
day(DATE(DATE#( [Date of last purchase], 'DD-MMM-YY'))) as [Day of last purchase],
[Value of last purchase],
[Number of purchases in cycle],
[Value of purchases in cycle],
[Perm to increase limit],
[Perm to sms],
[Perm to email],
[Perm to telemark],
[Postal code],
[Work phone],
[Home phone],
[Cell phone],
[Title code],
[First name],
[Last name],
[Address line] as [Address line 1],
[Address line] as [Address line 2],
City,
Province,
[Dtorder flag],
[Bank for debit order],
[Bank code for debit order],
[Bank account for debit order],
DATE(DATE#( [Date of charge off], 'DD-MMM-YY')) as [Date of charge off],
[OTB (incl. oversell)],
[Credit utilization],
[Days before activation],
[Ethnic group],
Gender,
Age,
[Payment profile month01],
[Payment profile month02],
[Payment profile month03],
[Payment profile month04],
[Payment profile month05],
[Payment profile month06],
[Payment profile month07],
[Payment profile month08],
[Payment profile month09],
[Payment profile month10],
[Payment profile month11],
[Payment profile month12],
[Payment profile month13],
[Payment profile month14],
[Payment profile month15],
[Payment profile month16],
[Payment profile month17],
[Payment profile month18],
[Payment profile month19],
[Payment profile month20],
[Payment profile month21],
[Payment profile month22],
[Payment profile month23],
[Payment profile month24],
[RG status at appl time],
[Interest rate current],
[Appl no],
[Comms language code],
[A+ insurance added],
DATE(DATE#( [Date A+ added], 'DD-MMM-YY')) as [Date A+ added],
DATE(DATE#( [date A+ cancelled], 'DD-MMM-YY')) as [date A+ cancelled],
[No of return mails],
[Wo reason code],
[Company Code],
DATE(DATE#( [Date of birth], 'DD-MMM-YY')) as [Date of birth],
[Language code],
[Statement indicator],
[Email address],
Employer,
Occupation,
[Spouse name],
[Spouse last name],
[Spouse home phone],
[Reference first name],
[Reference last name],
[Reference home phone],
[Campaign Reason],
[Reference No],
[Champion Challenge No],
[Offered Limit],
[Final Limit]
FROM
[Account Extracts\Daily_AccountExtract_A_11-APR-2011_12042011020121.txt]
(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines);

[Miguel_Angel_Baeyens]

Hello,

Can you change the name of the field in the source file? May be as easy as that.

Unfortunately, there's no other way. In the LOAD part, there's no way to identify which address field are you pulling, it's kind of luck that it doesn't give you an error (as it does when, for example, as a result of a SQL SELECT query to a SQL Server, there are more than one fields named alike).

If you don't use labels, then all fields are named @1, @2, and so on. But you cannot use labels for one fields and not for the others.

Hope that helps.

[Miguel_Angel_Baeyens · Accepted solution]

Hello,

Can you change the name of the field in the source file? May be as easy as that. Note that you can set "Explicit Labels" in the "Table Files" button dialog where you can specify the name for each field.

Unfortunately, there's no other way. In the LOAD part, there's no way to identify which address field are you pulling, it's kind of luck that it doesn't give you an error (as it does when, for example, as a result of a SQL SELECT query to a SQL Server, there are more than one fields named alike). EDIT: It does if fields are not renamed.

If you don't use labels, then all fields are named @1, @2, and so on. But you cannot use labels for one fields and not for the others.

Hope that helps.

[gerhardl]

I guess I can, but the file itself is too big to open in excel or even notepad without loooong delays, plus I would have to do it every day. I guess I can log an issue with IT so they change it in the database table but that might take weeks to finally happen...

Oh well.... Thanks for your help anyway, I'll have to take the long route then.

G

[patricio]

As mentinonned before, changing the field names in the source file should be the best solution. If you do not want to do that you can use Miguel Angel's solution just to load 3 fields without using labels, your key , address 1 and address 2. Then you load the file a second time with all the fields (except Address 1 and Address 2 using labels and joining it to the first load.

[#9]

Hi

I made a test and find this way to rename the Adress-field.

I describe in the attach document.

Plese have a look.

Regards

Anders