Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Code in Oracle

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(='0','Non Time Loss') as [Non Time Loss],     // Wher X is another column
  2.     if(>'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

8 Replies
JustinDallas
Specialist III
Specialist III

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

maxgro
MVP
MVP

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

;

Anonymous
Not applicable
Author

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(='0','Non Time Loss') as [Non Time Loss],     // Wher X is another column
  •     if(>'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


maxgro
MVP
MVP

Did you get an error?

Could you explain the output you want?

Maybe this

Table1:

Load

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

     if(>'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;

Anonymous
Not applicable
Author

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


maxgro
MVP
MVP

if the data comes from an Oracle database maybe

LAST2

uppercase

big_dreams
Creator III
Creator III

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

Anonymous
Not applicable
Author

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