11 Replies Latest reply: Feb 21, 2012 6:59 AM by Gregor Heuer RSS

    Leading Zeros, load text(...) and functions in sql select(...)

      Hello everyone,

       

      sorry for opening the nth Thread about coping with leading zeros, but i have trouble on the finishing line with the information i found so far.

       

      I found a few threads concerning loading data when keys can be "1", "01" or "001" an mean different things.

      The (almost) canonical answer to the question i found is:

      "load text(column) as column;

      sql select column from table;"

       

      For this example (extracting a column 1:1 into qlikview), it works perfectly.

       

      However, i have a slight problem in my script, as a lot of functions (especially nvl, decode, ltrim/rtrim) are called in sql.

      For example, one line in my script right now is:

       

      sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

       

      (nvl is an oracle function that gives possible_entry1 if it is not NULL and possible_entry2 otherwise).

      Now i have the same problem again: possible_entry1 and possible_entry2 can both contain "1" and "001" meaning different things.

       

      however:

       

      load text(used_entry) as used_entry;

      sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

       

      gives an error when executing the "sql"-Line:

      Field not found - <used_entry>

       

      How can I apply the Text-Function in this example?

       

      "load text (nvl(...))" does not work because the Qlik-View does not know about nvl, this part is done on the oracle-side.

        • Re: Leading Zeros, load text(...) and functions in sql select(...)

          you can use:

          if(isNull(used_entry), 'value', text(used_entry)) as used_entry

            • Re: Leading Zeros, load text(...) and functions in sql select(...)
              Celambarasan Adhimulam

              Hi,

                   Try with this

                  

              load text([used_entry]) as UsedEntry;

              sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

               

              May be its because same field name usage.

               

              Celambarasan

                • Re: Leading Zeros, load text(...) and functions in sql select(...)

                  load text([used_entry]) as UsedEntry did not work, sorry.

                  I got the same error as before.

                   

                  As for moving the logic out of the sql part into the load-statement as Federico Levi suggested, this would be the last resort. The nvl(..) line is just an example from the whole script, and i would rather avoid rewriting every sql-statement in corresponding functions in qlikview.

                   

                  What I do not understand is that the error comes up when executing the sql select, and not the load-statement.

                  If i leave the load-Statement out completeley, the sql executes without error (but mixes the '1' and '001' up).

                    • Leading Zeros, load text(...) and functions in sql select(...)
                      jagan mohan rao appala

                      Hi,

                       

                      What error you are getting, attach the script or sample file.

                       

                      Regards,

                      jagan.

                        • Re: Leading Zeros, load text(...) and functions in sql select(...)

                          The error i get is "Field not found"

                           

                          The script (reduced to the important part):

                           

                          SET ThousandSep='.';
                          SET DecimalSep=',';
                          SET MoneyThousandSep='.';
                          SET MoneyDecimalSep=',';
                          SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
                          SET TimeFormat='hh:mm:ss';
                          SET DateFormat='DD.MM.YY';
                          SET TimestampFormat='DD.MM.YY hh:mm:ss[.fff]';

                          CONNECT TO [Provider=MSDAORA.1;Password=xxxx;User ID=xxxx;Data Source=m1db_tcp_entw];

                           

                          // works
                          load TEXT(ORT_VORWAHL) as ORT_VORWAHL;
                          sql select ORT_VORWAHL from m1ort;

                           

                          // does not work
                          load TEXT(ziffer) as ziffer;
                          sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

                            • Leading Zeros, load text(...) and functions in sql select(...)
                              jagan mohan rao appala

                              Hi,

                               

                              select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

                               

                              is the above query executes correctly in the db.  Make sure. 

                               

                              There is nothing wrong in your script;

                               

                              Regards,

                              Jagan.

                                • Leading Zeros, load text(...) and functions in sql select(...)

                                  sql executes correctly (see below), and if i leave out the "load"-statement, the scripts executes without error (but mixing up 0003 and 3).

                                   

                                   

                                  SQL> select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

                                  ZIFFER                                                                         
                                  -------                                                                        
                                  31138                                                                          
                                  31101                                                                          
                                  0003TK                                                                         
                                  0003                                                                           
                                  0003TK                                                                         
                                  0003                                                                           
                                  3
                                  [....]

                                  1617 Zeilen wurden ausgewählt.

                                  SQL> spool off

                                    • Leading Zeros, load text(...) and functions in sql select(...)
                                      jagan mohan rao appala

                                      Hi,

                                      Try like this

                                       

                                      TempTable:

                                      sql select

                                                nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer,

                                                1 AS Temp

                                           from m1bhlst;

                                       

                                       

                                      TableName:

                                      LOAD

                                           Text(ziffer) AS ziffer

                                      RESIDENT TempTable;

                                       

                                      DROP TABLE TempTable;

                                       

                                      Hope this helps you.

                                       

                                      Regards,

                                      Jagan.

                                        • Leading Zeros, load text(...) and functions in sql select(...)

                                          I thought that this way the values with leading zeros would be thrown together in "TempTable" already, but I tried it anyway, and now i get the same error "Field not found <ziffer>" during the load-Statement in "TableName:".

                                           

                                          Right now i am really confused, i even get this error with:

                                           

                                          TempTable:

                                          select 'test' as a from dual;

                                          load a as b resident TempTable;

                                           

                                           

                                          ... which is almost straight out of the QV-Help to the "Load"-Command. I think I am doing something really wrong, but i cannot see it.

                                            • Leading Zeros, load text(...) and functions in sql select(...)
                                              j i

                                              GHR, I think I got the problem.

                                               

                                              Qlik is CASE SENSITIVE with the field names and when you use "... as used_entry" it converts it to USED_ENTRY. So try this:

                                               

                                              load text(USED_ENTRY) as used_entry;

                                              sql select nvl(possible_entry1,possible_entry2) as used_entry from table;

                                               

                                              Let me know how it goes

                                                • Leading Zeros, load text(...) and functions in sql select(...)

                                                  qlikuser14, you are right. I was so concentrated on the nvl(..) call as the difference between the working statement and the other that i missed the real problem (that the working part had all fieldnames completely in uppercase).

                                                   

                                                  The sample script works as below, preserving '003' and '3' as distinct values.

                                                  I will now go to the real script, but i do not expect any problems. Thanks a lot to all of you, especially jagan and qlikuser14.

                                                   

                                                   

                                                   

                                                   

                                                   

                                                  SET ThousandSep='.';
                                                  SET DecimalSep=',';
                                                  SET MoneyThousandSep='.';
                                                  SET MoneyDecimalSep=',';
                                                  SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
                                                  SET TimeFormat='hh:mm:ss';
                                                  SET DateFormat='DD.MM.YY';
                                                  SET TimestampFormat='DD.MM.YY hh:mm:ss[.fff]';

                                                  CONNECT TO [Provider=MSDAORA.1;Password=xxxx;User ID=xxxx;Data Source=m1db_tcp_entw];

                                                  // works
                                                  load TEXT(ORT_VORWAHL) as ORT_VORWAHL;
                                                  sql select ORT_VORWAHL from m1ort;

                                                   

                                                  // would not work
                                                  //load TEXT(ziffer) as ziffer;
                                                  //sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ziffer from m1bhlst;

                                                   

                                                  // this does work
                                                  load TEXT(ZIFFER) as ZIFFER;
                                                  sql select nvl(m1bhlst.leitziffer,m1bhlst.f5001) as ZIFFER from m1bhlst;