How to create a dimension in script based on the list loaded

[#1]

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

[#2 · Accepted solution]

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

[#3]

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