Move Current AGGR dimension to Data Load Editor

[Goncalo]

Hi Everyone,

I have a dimension that checks if there is at least one invoice item with a PO number. If there is one, then all invoice items are flagged as Yes. 

The formula used is the following (this is a dimension because I may need to filter the Yes or No transactions).

Field: Invoice with PO

=if(isnull(Aggr(count ({<[PO Number]={"=Len([PO Number])>1"}>}total <[Invoice Number]> [PO Number]), [Invoice Number])),'No','Yes')

Now I need to consider the result of the Invoice with PO field and combine with other variables. The intended logic is the following:

• If Invoice with PO is equal to 'Yes', I need to check if the PO number field is different than '-'. If it is, then the result should be 'Yes', if not, then it should be 'No'
• If Invoice with PO is equal to 'No', then it is always 'Yes'.

I leave below an illustration of the expected outcome.

I would also like to have this new column as a Dimension. I tried to combine the formula above with the additional logic but the performance decreased significantly.

I was thinking of moving the Invoice with PO  in to the Data Load Editor so that I could use if to create additional dimensions, but I was not able to create it. Both fields are from the table 'Facts'.

Could you please help me with this? Also, if you have a different way of creating this using dimensions it is also fine, I was just not able to do it.

Thanks.

[justISO · Accepted solution]

Hi, it depends on your data model structure, but how I would do, if there are only one table with 3 columns, 'Invoice Number', 'Invoice Item', 'PO number':

DATA_temp:
load * inline [
Invoice Number, Invoice Item, PO Number
1001,1001.01,2001
1001,1001.02,-
1001,1001.03,-
1002,1002.01,2002
1003,1002.01,-
];

InvoiceWpo:
LOAD
[Invoice Number],
count([PO Number]) as hasPoNo
RESIDENT DATA_temp
WHERE len([PO Number])>1
GROUP BY [Invoice Number];

LEFT JOIN (DATA_temp)
LOAD *
RESIDENT InvoiceWpo;

MAIN:
LOAD
[Invoice Number],
[Invoice Item],
[PO Number],
if(hasPoNo>0, 'Yes','No') as [Invoice with PO],
if((hasPoNo>0 and [PO Number]<>'-') or (isnull(hasPoNo)), 'Yes', 'No') as [Valid Invoice Item]
RESIDENT DATA_temp;

DROP TABLES DATA_temp, InvoiceWpo;

[Goncalo]

Hello,

Thank you for the quick and insightful reply! I did some adaptation to my data but it worked as expected!

Many thanks, marked as the solution.