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

If where and or

hi there,

I hope you can offer some assistance in the following:

The requirement is to import

  1. Invoices where there is an invoice number - not blank
  2. For only the account codes specified or Blank
  3. And not load those invoices where the specified names are in the Item Name

here is the code

where not IsNull([Invoice no.])
and
([Account Code]='61100000000' or '99999999999' or Len(Trim([Account Code])) = 0 )

and

not WildMatch ([Item Name],'*Carriage charges*','*Debenhams Sales Commission*','*Discount*','*Fees Chargeable Labelon Far East*','*Fees Chargeable Labelon Romania*','*Handling Charge*','*Invoice Adjustment*','*Of Right Transfer*','*Redelivery Carriage charges*','*Retro Chargeable Enam Labels*','*Retro*','*Chargeable Labelon Far East*','*Retro Chargeable Labelon Romania*','*Retro Chargeable Labelon Turkey*','*Return Carriage charges*','*Royalty*','*Sales Commission*','*Segura Charges December 2018*','*Segura Charges Decemeber 2018*','*Translation Costs*')

When scaled up my testing I found that all invoices loaded with account codes not specified in the list

To check this I reverted to the format I had in my original doc which didn’t include the part about loading blank invoices - Len(Trim([Account Code])) = 0

 

Here is the value I have replaced the above with:

WHERE not IsNull([Invoice no.]) and wildmatch ([Account Code],'*61100000000*','*99999999999*')

and not WildMatch ( [Item Name],'*Carriage charges*','*Debenhams Sales Commission*','*Discount*','*Fees Chargeable Labelon Far East*','*Fees Chargeable Labelon Romania*','*Handling Charge*','*Invoice Adjustment*','*Of Right Transfer*','*Redelivery Carriage charges*','*Retro Chargeable Enam Labels*','*Retro*','*Chargeable Labelon Far East*','*Retro Chargeable Labelon Romania*','*Retro Chargeable Labelon Turkey*','*Return Carriage charges*','*Royalty*','*Sales Commission*','*Segura Charges December 2018*','*Segura Charges Decemeber 2018*','*Translation Costs*')

//and Len(Trim([Account Code])) = 0

This is now loading the invoices for only the specified Account codes and giving the exclusions on the specified Item Name, however, I have been unable to get this to work with the addition of

 

Len(Trim([Account Code])) = 0

 

Please can people give suggestion on how i can write this to achieve the required.

Thank you in advance.

Daniel

Labels (2)
1 Solution

Accepted Solutions
davyqliks
Specialist
Specialist
Author

I thought i would inform i got it working using the following

where not IsNull([Invoice no.])

and

([Account Code]= ('5112100000') or [Account Code]= ('5112200000') or Len(Trim([Account Code])) = 0 )

and......

Thank you for the advice

Daniel

View solution in original post

7 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hi Daniel,

Let's try this:

Invoices where there is an invoice number - not blank:

 

 

Len(Trim([Invoice no.]))>0

 

 

For only the account codes specified or Blank:

 

 

WildMatch(Trim([Account Code]), '61100000000','99999999999','')

 

 

 And not load those invoices where the specified names are in the Item Name:

 

 

WildMatch ([Item Name],'*Carriage charges*','*Debenhams Sales Commission*','*Discount*','*Fees Chargeable Labelon Far East*','*Fees Chargeable Labelon Romania*','*Handling Charge*','*Invoice Adjustment*','*Of Right Transfer*','*Redelivery Carriage charges*','*Retro Chargeable Enam Labels*','*Retro*','*Chargeable Labelon Far East*','*Retro Chargeable Labelon Romania*','*Retro Chargeable Labelon Turkey*','*Return Carriage charges*','*Royalty*','*Sales Commission*','*Segura Charges December 2018*','*Segura Charges Decemeber 2018*','*Translation Costs*')

 

 

 

Combine those together with 'AND' statements and you get:

 

Len(Trim([Invoice no.]))>0
AND WildMatch(Trim([Account Code]), '61100000000','99999999999','')
AND WildMatch ([Item Name],'*Carriage charges*','*Debenhams Sales Commission*','*Discount*','*Fees Chargeable Labelon Far East*','*Fees Chargeable Labelon Romania*','*Handling Charge*','*Invoice Adjustment*','*Of Right Transfer*','*Redelivery Carriage charges*','*Retro Chargeable Enam Labels*','*Retro*','*Chargeable Labelon Far East*','*Retro Chargeable Labelon Romania*','*Retro Chargeable Labelon Turkey*','*Return Carriage charges*','*Royalty*','*Sales Commission*','*Segura Charges December 2018*','*Segura Charges Decemeber 2018*','*Translation Costs*')

 

Another option with OR statement:

Len(Trim([Invoice no.]))>0
AND (WildMatch(Trim([Account Code]), '61100000000','99999999999') OR Len(Trim([Account Code]))=0)
AND WildMatch ([Item Name],'*Carriage charges*','*Debenhams Sales Commission*','*Discount*','*Fees Chargeable Labelon Far East*','*Fees Chargeable Labelon Romania*','*Handling Charge*','*Invoice Adjustment*','*Of Right Transfer*','*Redelivery Carriage charges*','*Retro Chargeable Enam Labels*','*Retro*','*Chargeable Labelon Far East*','*Retro Chargeable Labelon Romania*','*Retro Chargeable Labelon Turkey*','*Return Carriage charges*','*Royalty*','*Sales Commission*','*Segura Charges December 2018*','*Segura Charges Decemeber 2018*','*Translation Costs*')

 

davyqliks
Specialist
Specialist
Author

Hi Stoyan,

Thank you so much for the reply and insight.

Unfortunately both of the complete scripts are returning only the Account code 

99999999999

Thank you for your help 

Daniel

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hi Daniel,

 

Few questions that can help you troubleshoot:

 

Is it possible that this is the only Account code retrieving data given the rest of the criteria?

If you remove the filter with the Account codes, can you find the code 61100000000 in a list box?

Can you post the output of this Load statement:

Test:

LOAD DISTINCT

[Account code]

From YourSource.qvd (qvd);

 

Kind regards,

S.T.

davyqliks
Specialist
Specialist
Author

Hi,

No, afraid not,

I have had it working without the len function with the same data.

I have also had it working with Len but not excluding any account codes.

Thanks though, really appreciated.

Daniel

 

davyqliks
Specialist
Specialist
Author

I thought i would inform i got it working using the following

where not IsNull([Invoice no.])

and

([Account Code]= ('5112100000') or [Account Code]= ('5112200000') or Len(Trim([Account Code])) = 0 )

and......

Thank you for the advice

Daniel

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hi Davyqliks,

 

I'm happy you are satisfied with the results!

Just remember, where not IsNull([Invoice no.]) will also retrieve empty string '', also may retrieve 0 or any other wild character you'd wish to exclude. You may wanna check on this.

Also, check for consistency between the Account Codes (I think you used different ones in the original example)

Other lines look good!

 

Kind regards,

S.T.

 

davyqliks
Specialist
Specialist
Author

Sorry for delay in reply, Thank you so much for your help.

I hope you may be able to assist with another small issue.

I have not used max Date before and always fixed data with set.

I have sorted my date import calendar and want to implement some dynamic date fields in tables.

when i use the following i am getting the max date returned

Date(Max([Voucher Date]))

 

I would like to use this with a basic sum  of

Sum(HKD_Price)

But i am struggling to get a result when trying to glue the 2 together

Can you assist with this?

Thank you in advance Stoyan

Daniel