topic Re: How to create a dimension in script based on the list loaded in QlikView

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

Fri, 19 Apr 2013 21:31:44 GMT

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

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

Thu, 25 Apr 2013 09:28:41 GMT

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

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

Thu, 25 Apr 2013 17:09:44 GMT

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