Set Expression based on dimension

[marishnagendran]

Dear All,

	Fact Table
No	Customer_Num	Source	Branch	Sales	Delay Reason
1	A	sales	Branch 1	20
2	B	sales	Branch 1	10
3	C	sales	Branch 1	20
4	A	sales	Branch 1	10
1	A	Stock	branch 1	-	Delivered
2	B	Stock	branch 1	-	Delivered
3	C	Stock	branch 1	-	Delay
4	A	Stock	branch 1	-	Not Delivered

The above is my data model.It contains Sales and Stock fact table concatenate into 1 fact table.

In table chart 

1. Manual No
2. Customer Num
3. Branch
4. No.of.Sales (Measure)

In above table chart i need to add the Delay reason as a 5th column based on Manual No.

Whether it is possible or not???

[frederikverhaeghe]

hello,

 could you share your load script?

[marishnagendran]

Thanks for your response.

It contains too many fields and also confidential.

But it is the requirement?? Do you have any doubts on this???

[frederikverhaeghe]

No

Customer_Num

Source

Branch

Sales

Delay Reason

Do I understand it correctly that you have a sales table, containing

• No
• Customer_Num
• Branch
• Sales

and a stock table containing

• No
• Customer_Num
• Branch
• Delay reason

?

[marishnagendran]

Yeah Correct!!!!

[frederikverhaeghe]

Assuming No. is the unique identifying field, I would try the following:

load No, Customer_Num, Branch, Sales

from sales_table

load No,  Delay reason

from stock_table

what do you think?

[marishnagendran]

I'm already done data modelling.Just see my first post

I need output like,

Manual No	Customer Number	Branch	Sum of Sales	Delay Reason
1	A	Branch 1	20	Delivered
2	B	Branch 1	10	Delivered
3	C	Branch 1	20	Delay
4	A	Branch 1	10	Not Delivered

Manual no is unique for Sales and Stock table

It is possible???

[robert99]

"Is it possible"

It is

Its best to do this in script though as explained above.

But if this is not possible

Set up a table using

• Dimensions = loadNo, Customer_Num, Branch
• a  measure where Sum ({< Source = {Sales}>} Sales)
• an aggr statement >> aggr( delayreason   ,loadNo)

You can restrict delayreason by using set analysis (I think)

Aggr({<Source  = {Stock}>}  delayreason   ,loadNo)

[marishnagendran]

I tried AGGR function but it is not working