Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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?
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
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,
yes, you are right I didn't see this
Regards
I am loading from a .mdb database from qliksense. I have attached a screen shot how everything looks on the load script.
Where do I copy paste this into?
into the Main? or seperate tables I created?
And where do I put the Where?
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
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