match function

[#1]

Hi,

I am stuck in one logic,please help me to resolve that issue.

I have two fact table which are link to each other with the help of key,some rows are not mapped due to key are missing in one table.

I want sales of those employee whose key are matched with each table.

Thanks in advance.

Regards,

Nisha

[Anonymous]

sample data please

[fashid]

sample QVW please

[#4]

For example

Table1:

key Employee Name Sales

1 A 100

2 B 200

3 C 500

Table2:

key Dept Name Sales1

1 X 100

2 Y 200

3 Z 500

4 W 600

I want sum(Sales)=800 and Sum(Sales1)=800...means only 3 keys are match these two table, so it gives only those sales whose key are match.

[arulsettu]

Hi

concatenate the two fact tables it may be help

[Anonymous]

Check if this is helps

Fact1:

LOAD * INLINE [

     Employee, Name, Sales,

1, A, 100

2, B, 200

3, C, 500

];

Fact2:

LOAD * INLINE [

    Dept, Name, Sales1,

1, X, 100

2, Y, 200

3, Z, 500

4, W, 600

];

Fact_Drop:

NoConcatenate

load

* Resident Fact1;

Right join

load

Dept as Employee,

Name as Name2,

Sales1

Resident Fact2;

drop tables Fact1,Fact2;

Additional_record_in_Fact2:

NoConcatenate

load

Employee as Dept,

Name2 as Name,

Sales1

Resident Fact_Drop

where Len(Sales)=0;

drop Table Fact_Drop;

Regards

Harsha

[#7]

I want that logic in sheet level.

[fashid]

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Fact1:

LOAD * INLINE [

     Employee, Name, Sales,

1, A, 100

2, B, 200

3, C, 500

];

Concatenate

Fact2:

LOAD * INLINE [

    Dept, Name, Sales1,

1, X, 100

2, Y, 200

3, Z, 500

4, W, 600

];

use this

and then in front end chart sum(sales)

and sum(sales 1)

i have attached the app

[Anonymous]

Hi Nisha

I managed to get the expression

please see wether it is helpfull

=if(match(ValueList($(=concat({1} distinct chr(39) & Employee & Chr(39),','))),ValueList($(=concat({1} distinct chr(39) & Dept & Chr(39),','))))=0,sum(Sales1),sum(Sales1)-(sum(Sales1)-sum(Sales)))

Regards

Harsha

[#10]

Hi Harsha,

I want to  make a chart in which Dept is in x-axis,I want sales for all department ,but whose key are matched with table 1 it will show that sum of sales else it show as it is which is in table 2.

Regards,

Nisha