Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a big data base with accounting dimensions that i load to QV. I would like to sort out a part of them in my analysis. The problem is that there is no simple rule to do this. The simplest I found, covering all transactions I need to filter out is a combination of transaction number and company code and year. I have all unique combinations that I need (over 400) in the separate file.
What I was thinking about is to create a separate dimension in the script, that would show ... For instance "YES" in case of database transactions being on my list, and "NO" for those that are not there. I would like to Asi for your help in changing my wish into the proper code
Thanks a lot in advance.
I attach the example in excel of what I need to do in QV. It could be easier to present this thaw way
Thanks a lot for any feedback
Hi,
if I understood you right, you need a new combined key to filter the according transactions similar to this:
// First Load the date you want to load:
FilterData:
LOAD
transaction1 & '-' &
Company1 & '-' &
Year1 & '-' &
Month1 AS FilterKey
FROM
(biff, embedded labels, header is 1 lines, table is Sheet1$);
Data:
LOAD Account,
Month,
Year,
Posting_Date,
transaction,
Company,
Material,
Supplier,
Customer,
Amount,
IF(Exists(FilterKey, transaction & '-' & Company & '-' & Year & '-' & Month), 'YES', 'NO')
AS [My Transactions]
FROM ...
Or even better: filter the source data during the second load like this:
FROM .....
Where Exists(FilterKey, transaction & '-' & Company & '-' & Year & '-' & Month);
HtH
Roland
Hi,
if I understood you right, you need a new combined key to filter the according transactions similar to this:
// First Load the date you want to load:
FilterData:
LOAD
transaction1 & '-' &
Company1 & '-' &
Year1 & '-' &
Month1 AS FilterKey
FROM
(biff, embedded labels, header is 1 lines, table is Sheet1$);
Data:
LOAD Account,
Month,
Year,
Posting_Date,
transaction,
Company,
Material,
Supplier,
Customer,
Amount,
IF(Exists(FilterKey, transaction & '-' & Company & '-' & Year & '-' & Month), 'YES', 'NO')
AS [My Transactions]
FROM ...
Or even better: filter the source data during the second load like this:
FROM .....
Where Exists(FilterKey, transaction & '-' & Company & '-' & Year & '-' & Month);
HtH
Roland
Thanks a lot Roland,
I can not try imediatelly your sollution, as I have QV instaled on different machine, but this looks good.
I was thinking about something like left or right join, but did not know how to finalize this. Your example is much more somple than my idea...
thanks