3 Replies Latest reply: Sep 12, 2012 4:04 AM by Maarit Sailynoja RSS

    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

        • Re: Field names turned in to uppercase automatically

          did you try

           

           

          rename field DITEMCODE to DItemCode;

          • Re: Field names turned in to uppercase automatically
            Jonathan Dienst

            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

            • 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