Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Field names turned in to uppercase automatically

Hi,

I have following problem. I have data sources of 2 different types: SQL Server and progress database. The problem is that when I try to make joining fields based on the excatly same name, QlikView changes progress database's filed names to upper case and then the joining fails between the tables.

Below is simple example what I'm trying to do. I noticed that I had the same problem with some Excel-files. I know that I could solve this problem by changing the SQL Server fields to uppercase with AS word but I have already made a lot with only the SQL Server data and I would need to correct loads of objects in my QlikView file if I would change the field names.

Hopefully somebody knows what is the issue in here.

EXAMPLE (PROD is SQL Server and POW is progress db):

ODBC

CONNECT32 TO PROD (XUserId is ***, XPassword is **);

LOAD *,


(
DCustName & ' / ' & Text(DCustNum)) AS DCustomer;



SQL SELECT CustName AS DCustName,

    

     CustNum AS DCustNum,


    Factory,

    Item AS DItemCode

FROM Tuotanto.dbo."ACS_Main";

ODBC

CONNECT32 TO POW (XUserId is **, XPassword is **);


SQL SELECT CtrlType as ITCtrlType,

    ItemCode as DItemCode,

    ItemName as ITItemName

FROM PUB.Item;

-->The result is that ACS_Main fields will be in table view exactly like stated (DCustName, DCustNum, Factory, DItemCode) but PUB.Item fiels are in uppercase (ITCTRLTYPE, DITEMCODE,ITITEMNAME)

I would like to link the tables based on the DItemCode

1 Solution

Accepted Solutions
Not applicable

Re: Field names turned in to uppercase automatically

Hi,

sorry that my reply took this long. I have been out of office and didn't have possibilities to reply.

Renaming helped me to proceed and like J.D. mentioned the loading order was the main thing. I tried different loading orders and with that I got this working.

Thanks for pushing me to the right direction.

BR,

Maarit

3 Replies
Not applicable

Re: Field names turned in to uppercase automatically

did you try

rename field DITEMCODE to DItemCode;

MVP
MVP

Re: Field names turned in to uppercase automatically

Hi

The way I see it you have two choices - either alias the fields to be the same, or rename after loading with a rename map.

If you choose the first, you use As aliases in the load:

     LOAD ITEMID As ItemID,

          ITEMNAME As ItemName,

          ...

To rename using a map, create an inline or file sourced mapping table with two columns, ProgressName and SQLName, like this:

     FieldMap:

     Mapping LOAD * Inline

     [

          Progress, SQL

          ITEMID, ItemID

          ITEMNAME, ItemName

          ....

     ];

Load the Progress data first, and then after loading (before loading the SQL data):

     Rename fields using FieldMap;

(You cannot rename a field into an existing name, so any renaming must be done before the other data is loaded.)

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Field names turned in to uppercase automatically

Hi,

sorry that my reply took this long. I have been out of office and didn't have possibilities to reply.

Renaming helped me to proceed and like J.D. mentioned the loading order was the main thing. I tried different loading orders and with that I got this working.

Thanks for pushing me to the right direction.

BR,

Maarit

Community Browser