Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Using Where Clause to filter data in load script, Need help please

I am trying to filter data that I load by mimicking these scripts (See Below):


I am not sure where to start but I believe we should use a WHERE clause in the Load Script.




Note:  SLSNR1=First character of SLSNR field.

 

IF        SLSNR1=7 Intercompany

Or SLSNR1=8 Nasco West

Or SLSNR1=9 Arnold Nasco

Or SLSNR>=11 and SLSNR<=19 Store account Sales  

Or CUSNR=66660100 Nasco Plastics       

Or CUSNR>=99950100 Triarco

Or CUSNR>=99990100 and CUSNR<=99990199       I/C-Nasco Modesto    

Or CUSNR>=99990200 and CUSNR<=99990299       I/C Nasco

Or CUSNR>=99990500 and CUSNR<=99990599       Store transfer

Or CUSNR>=99990800 Employee Pricing     

Or ITLOC='RETRN' Returns

eval      VALID=*BLANKS 

  endif              

Thanks!

8 Replies
alextimofeyev
Contributor II

Re: Using Where Clause to filter data in load script, Need help please

Are you loading from SQL, i.e. will this WHERE clause be a part of the SQL query? If not, what is the data source?

martinpohl
Valued Contributor II

Re: Using Where Clause to filter data in load script, Need help please

hello,

use this:

IF       ( SLSNR1=7 ,'Intercompany',

if (SLSNR1=8, 'Nasco West',

if (SLSNR1=9 'Arnold Nasco',

if (SLSNR>=11 and SLSNR<=19, 'Store account Sales',  

if (CUSNR=66660100 ,'Nasco Plastics',      

if (CUSNR>=99950100 ,T'riarco',

if (CUSNR>=99990100 and CUSNR<=99990199,       'I/C-Nasco Modesto',

if (CUSNR>=99990200 and CUSNR<=99990299,       'I/C Nasco',

if (CUSNR>=99990500 and CUSNR<=99990599,       'Store transfer',

if (CUSNR>=99990800 , 'Employee Pricing',     

if (ITLOC='RETRN', 'Returns', ' '))))))))))) as Fieldname,


Regards

srchilukoori
Valued Contributor

Re: Using Where Clause to filter data in load script, Need help please

Martin,

A small change in the CUSNR value order. In your case all CUSNR >= 99950100 will be classified as "T'riarco',",

IF       ( SLSNR1=7 ,'Intercompany',

if (SLSNR1=8, 'Nasco West',

if (SLSNR1=9 'Arnold Nasco',

if (SLSNR>=11 and SLSNR<=19, 'Store account Sales',  

if (CUSNR=66660100 ,'Nasco Plastics',      

if (CUSNR>=99990800 , 'Employee Pricing',

if (CUSNR>=99990500 and CUSNR<=99990599,       'Store transfer',

if (CUSNR>=99990200 and CUSNR<=99990299,       'I/C Nasco',

if (CUSNR>=99990100 and CUSNR<=99990199,       'I/C-Nasco Modesto',     

if (CUSNR>=99950100 ,T'riarco',

if (ITLOC='RETRN', 'Returns', ' '))))))))))) as Fieldname,

martinpohl
Valued Contributor II

Re: Using Where Clause to filter data in load script, Need help please

yes, you are right I didn't see this

Regards

Not applicable

Re: Using Where Clause to filter data in load script, Need help please

I am loading from a .mdb database from qliksense. I have attached a screen shot how everything looks on the load script.

1.png

2.png

Not applicable

Re: Using Where Clause to filter data in load script, Need help please

Where do I copy paste this into?

into the Main? or seperate tables I created?

1.png


And where do I put the Where?

Re: Using Where Clause to filter data in load script, Need help please

try creating field in script as below

IF( SLSNR1='7', 'Intercompany',

if( SLSNR1='8', 'Nasco West',

if( SLSNR1='9', 'Arnold Nasco',

if( SLSNR>=11 and SLSNR<=19, 'Store account Sales',

if( CUSNR='66660100', 'Nasco Plastics',      

if( CUSNR>=99950100 ,'Triarco',

if( CUSNR>=99990100 and CUSNR<=99990199 ,      'I/C-Nasco Modesto '  ,

if( CUSNR>=99990200 and CUSNR<=99990299 ,      'I/C Nasco',

if( CUSNR>=99990500 and CUSNR<=99990599 ,      'Store transfer',

if( CUSNR>=99990800, 'Employee Pricing',    

if( ITLOC='RETRN' 'Returns',' '))))))))))) as FieldName

martinpohl
Valued Contributor II

Re: Using Where Clause to filter data in load script, Need help please

within your data model, it won't work as scrpted before.

you need all fields you want to compare wihtin one table.

it looks like that the orderd table is the right one.

applymap all details you need from the other tables into that table (you don't need to load them as separated fields, only for your if-statement)

use mapping load and applymap so all your datas are in.

Regards