8 Replies Latest reply: Feb 2, 2018 4:42 AM by Lakshman S RSS

    Code in Oracle

    Lakshman S

      Hi Experts,

       

      I am new to QLikSense and I want to know what is the corresponding code in Oracle. In the data load editor, i was able to create the columns on the fly when I was adding data from Excel. but when I try to connect the data to Oracle and then when I try to create the following columns I am getting an error in data load editor. Could someone please tell me what is the corresponding code that i need to give in data load editor so that it will work fine while importing the data from Oracle Database.

      This is the code in data load editor while importing the data from Excel.

       

       

      1.     if([X]='0','Non Time Loss') as [Non Time Loss],     // Wher X is another column
      2.     if([X]>'0','Time Loss') as  [Time Loss],       // Wher X is another column
      3.     Right(SEASON,2) as Last2,    // This would give the value as 13 if SEASON is 2013
      4.     SEASON & '-' & (Last2+1) as Season_label    //This wuld show up something like 2013-14 if SEASON is 2013

      If(SEASON_SCHEDULE='OS',1,

      If(SEASON_SCHEDULE='PS',2,

      If(SEASON_SCHEDULE='RS',3,4))) as SortOrder  // This is useful for Sorting.

      FROM [lib://AttachedFiles/NBA_Injury_24Jan2018.xls]

      Where SEASON>2012 and SEASON<>9999;





      Thanks,

      Lakshman

        • Re: Code in Oracle
          Justin Dallas

          Since you are having trouble with your Oracle code, why don't you show us your Oracle code?

          • Re: Code in Oracle
            Massimo Grossi

            You can use a preceding load:

            Preceding Load

            a simple SQL select from Oracle database

                 SQL SELECT

                      ORACLEFIELD1, ORACLEField2, ........

                 FROM ORACLETable

                 Where SEASON>2012 and SEASON<>9999

                 ;



            Then add a load statement, maybe the same you used for excel

            Your final script should be something like

            LOAD

                 If(SEASON_SCHEDULE='OS',1,

                 If(SEASON_SCHEDULE='PS',2,

                 If(SEASON_SCHEDULE='RS',3,4))) as SortOrder  // This is useful for Sorting.

                 .....

                 .....

            ;

            SQL SELECT

                 ORACLEFIELD1, ORACLEField2, ........

            FROM ORACLETable

            Where SEASON>2012 and SEASON<>9999

            ;

              • Re: Code in Oracle
                Lakshman S

                Hi Massimo

                 

                Thanks for your reply. I was able to get all the thing work except for

                1.     SEASON & '-' & (Last2+1) as Season_label    //This wuld show up something like 2013-14 if SEASON is 2013

                I am not sure what is wrong with the code. It works fine  when I add something like

                 

                Table1:

                Load

                •    if([X]='0','Non Time Loss') as [Non Time Loss],     // Wher X is another column
                •     if([X]>'0','Time Loss') as  [Time Loss],       // Wher X is another column
                • Right(SEASON,2) as Last2,    // This would give the value as 13 if SEASON is 2013

                FROM [lib://AttachedFiles/NBA_Injury_24Jan2018.xls]

                Where SEASON>2012 and SEASON<>9999;


                Tabel2:

                load*,

                SEASON & '-' & (Last2+1) as Season_label    //This would show up something like 2013-14 if SEASON is 2013

                Resident Table1;

                Drop Table1;


                This is happening even when I add the data from excel. so can you please let me know how to get it done without adding extra code as I mentioned here. i.e. I need to able to add all the fields in one load statement.


                Thanks,

                Lakshman


                  • Re: Code in Oracle
                    Massimo Grossi

                    Did you get an error?

                    Could you explain the output you want?

                    Maybe this

                     

                    Table1:

                    Load

                         if([X]='0','Non Time Loss') as [Non Time Loss],    

                         if([X]>'0','Time Loss') as  [Time Loss],      

                         SEASON & '-' & Right(SEASON,2) +1  as Season_label   

                    FROM [lib://AttachedFiles/NBA_Injury_24Jan2018.xls]

                    Where SEASON>2012 and SEASON<>9999;

                • Re: Code in Oracle
                  Lakshman S

                  Hi Massimo,

                   

                  I want the output as 2013-14 if the Season is 2013. In the expression

                  Right(SEASON,2) as Last2  // it will return the last 2 digits of SEASON ex. "13"  if SEASON is 2013 and the

                  Expression SEASON & '-' & (Last2+1)  as Season_label   would return 2013-14.


                  I am getting an error as "field not found" for Last2 when I put all these additional fields in one load statement.

                  But if I use two load statements then I am not getting the error.

                  I somehow want this to be in one load statement because I don't want to make the code look complex.


                  Thanks,

                  Lakshman


                    • Re: Code in Oracle
                      Massimo Grossi

                      if the data comes from an Oracle database maybe

                       

                      LAST2

                       

                      uppercase

                      • Re: Code in Oracle
                        max payne

                        Hi,

                         

                        If you are creating Last2 field in Preceding load then you can not use it in same load.

                        If you want use that newly created field in preceding load then, you have have apply one more preceding load over that to use it.

                         

                        like

                        Load *,Season & '-' & Last2+1 as Season_level;

                        Your Currentcode;

                        or

                         

                        If you want to multiple preceding load then try what Massimo suggested.

                         

                        Load

                        *,Season & '-' &right(Season,2)+1 as Season_level;

                        Sql Select * from SqlTable;

                         

                        Regards

                        • Re: Code in Oracle
                          Lakshman S

                          Ok Massimo,

                           

                          I think I need to go with two load statements because I am creating last2 field on the fly and also Season_label which is based on last2 and Season and that's the reason why I am getting the error.

                           

                          Thanks,

                          Lakshman