Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following section in my script:
LOAD
TRNumber,
CompCodeTRNumberKey,
RegistrationDate,
ReceiptComment,
TransactionDate as InvoiceDate,
year (TransactionDate) as InvoiceYear,
month (TransactionDate) as InvoiceMonth,
TransactionType,
Account
FROM [lib://Receipt.qvd]
(qvd)WHERE match(Account, 3040, 3042, 4643, 4644, 4645, 4646, 4647, 4648, 4649)and [TransactionDate]>MakeDate(2010);
Is there any way that I can make my date filter of Invoice Year and Invoice Month to be based on only account 3040 and 3042?
Thanks!
Like this?
WHERE
Match(Account, 3040, 3042, 4643, 4644, 4645, 4646, 4647, 4648, 4649)
OR ([TransactionDate]>MakeDate(2010) AND Match(Account, 3040, 3042))
No, I'm wondering if there's anyway to keep the script as it is and instead make a selection of accounts in the actual app where I create the datefilter?
make a selection of accounts in the actual app where I create the datefilter?
Sorry, but I have no idea what you want. At least, I assume you want to do something different then make a selection of accounts which can be done in a listbox with accounts as field.
Try Something like this
LOAD
TRNumber,
CompCodeTRNumberKey,
RegistrationDate,
ReceiptComment,
TransactionDate as InvoiceDate,
year (TransactionDate) as InvoiceYear,
month (TransactionDate) as InvoiceMonth,
TransactionType,
Account
FROM [lib://Receipt.qvd]
(qvd)WHERE match(Account, 3040, 3042, 4643, 4644, 4645, 4646, 4647, 4648, 4649)
and year (TransactionDate)+'-'+month (TransactionDate)
in (select distinct
year (TransactionDate)+'-'+month (TransactionDate)
from
[lib://Receipt.qvd]
(qvd)WHERE match(Account, 3040, 3042)
)
P.S. I dont know the exact syntax for QVD load, just giving a idea ( this kind of query will work with SQL server not sure about QVD load)
Thanks,
Lakshmikandh
Hi
may be like this :
If(Account='3040' and Account='3042,year (TransactionDate)) as InvoiceYear,
If(Account='3040' and Account='3042,month (TransactionDate)) as InvoiceMonth,
Thank you guys, I used Bruno Bertels ideá and put it in the function field for the filter.