topic Re: match function in QlikView

match function

Fri, 14 Nov 2014 11:39:03 GMT

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

Re: match function

Anonymous — Fri, 14 Nov 2014 11:39:56 GMT

sample data please

Re: match function

fashid — Fri, 14 Nov 2014 11:45:05 GMT

sample QVW please

Re: match function

Fri, 14 Nov 2014 11:46:45 GMT

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.

Re: match function

arulsettu — Fri, 14 Nov 2014 11:56:27 GMT

concatenate the two fact tables it may be help

Re: match function

Anonymous — Fri, 14 Nov 2014 12:00:52 GMT

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

Re: match function

Fri, 14 Nov 2014 12:13:29 GMT

I want that logic in sheet level.

Re: match function

fashid — Fri, 14 Nov 2014 12:19:29 GMT

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

Re: match function

Anonymous — Fri, 14 Nov 2014 14:36:52 GMT

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

Re: match function

Mon, 17 Nov 2014 07:00:37 GMT

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

Re: match function

PradeepReddy — Mon, 17 Nov 2014 07:37:03 GMT

try this expression....

=sum(if(not IsNull(Sales1) and not isnull(Sales), Sales1))

Note: I am assuming the values of 'Sales' and 'Sales1' for each key is same.

Thanks,

Pradeep

Re: match function

Anonymous — Mon, 17 Nov 2014 07:52:11 GMT

Hi,

Try something like below,

Sum ({$<[Employee Name]={"*"}>} Sales1)