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: 
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
Partner - Creator II
Partner - Creator II

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
Partner - Master
Partner - Master

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
Specialist
Specialist

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
Partner - Master
Partner - Master

yes, you are right I didn't see this

Regards

Not applicable
Author

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
Author

Where do I copy paste this into?

into the Main? or seperate tables I created?

1.png


And where do I put the Where?

Kushal_Chawda

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
Partner - Master
Partner - Master

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