Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi there,
I hope you can offer some assistance in the following:
The requirement is to import
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
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
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*')
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
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.
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
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
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.
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