Code in Oracle

[Anonymous]

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

[JustinDallas]

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

[maxgro]

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]

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]

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]

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]

if the data comes from an Oracle database maybe

LAST2

uppercase

[big_dreams]

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]

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