Request to resolve my challenging work.

[#1]

Hi All,

I have an excel file (Attached) which need to be loaded in QV in such a way that all the sub domain  which has domain engagement status as c (C represents 'current')in any account and there is an opportunity (domain engagement status as 'O' ) in another account.

The result must be given below

Sub Domain	Functional Area	Account	Domain Engagment Status
Payer	Membership	Kaiser	C
Payer	Membership	United Health Group	C
Payer	Membership	Humana CarePlus	C
Payer	Benefits Management	Humana CarePlus	C
Payer	Provider Management	United Health Group	C
Payer	Provider Management	Humana CarePlus	C
Payer	Medical Management	Humana CarePlus	O
Payer	Claim Adjudication	Humana CarePlus	C
Payer	Finance	Kaiser	C
Payer	Finance	Humana CarePlus	C
Payer	Underwriting & Actuaries	Humana CarePlus	O
Payer	Customer Service	Humana CarePlus	O
Payer	Customer Service	NEHEN and Rx Gateway	C
Payer	Sales & Marketing	Kaiser	C
Payer	EDI (HL7/X12)	Kaiser	C
Payer	EDI (HL7/X12)	Humana CarePlus	C
Payer	EDI (HL7/X12)	NEHEN and Rx Gateway	C
Payer	Claim Fraud Detection	Truven	C
Payer	Claim Audits	Truven	C
Payer	Health Plans	Truven	C
Payer	Analytics	Truven	C
Life Sciences	Research & Development	Pfizer	O
Life Sciences	Drug Discovery	Pfizer	O
Life Sciences	Clinical Trials	Pfizer	O
Life Sciences	Medical Info	Pfizer	O
Life Sciences	Quality & Manufacturing	Pfizer	O
Life Sciences	Quality & Manufacturing	Glenmark Generics	C
Life Sciences	Quality & Manufacturing	Pacific Biosciences	O
Life Sciences	Sales & Marketing	Pfizer	O
Life Sciences	Legal / Corporate	Pfizer	O

Thanks for you reply.

[vikasmahajan]

Prajeesh Kumar

You question is not clear what exact you want to achieve ?

Vikas

[jagan]

HI,

Try like this

Data:

LOAD

*

FROM ExcelFileName.xls;

INNER JOIN (Data)

LOAD DISTINCT

[Sub Domain]

RESIDENT Data

WHERE [Domain Engagment Status] = 'C';

Hope this helps you.

Regards,

Jagan.

[#4]

You can see in my excel file, there are 3 columns which are  "Sub Domain", "Functional Area", "Account" and "Domain  Engagment".

In Domain engagement column, the values are C, P, O.

I want to display all the subdomain, functional area, account columns in QV as straight table which has status as 'c' and also have an opportunity ( domain status as 'O').

Please look at the attached excel file and given result.

[#5]

Thanks for your reply.

I want to display all the columns - subdomain, functional area, account columns in QV as straight table which has status as 'c' and also have an opportunity ( domain status as 'O').

Please look at the attached excel file and given result.

[jagan]

Hi,

Add three dimensions and use this expression

=If(Count({<Account" and "Domain  Engagment={'O', 'C'}>} DISTINCT [Account" and "Domain  Engagment]) > 1, 1, 0)

Regards,

Jagan.

[NareshGuntur]

Hi Prajeesh,

I am not clear with your question but hope you are looking for something similar to below.

A where condition which will load only the Domain Engagement Status 'C' and 'O'.

LOAD [Sub Domain],

     [Functional Area],

     Account,

     [Domain Engagment Status]

FROM

qvformat.xlsx

(ooxml, embedded labels, table is Sheet1)

where Match([Domain Engagment Status],'C','O');

Best Regards,

Naresh

[#8]

Hi Jagan,

Expression showing syntax error. Please verify once again and send me. I hope, it may work.

[#9]

Hi Prajeesh,

you have to load your data as it is and use the Aggr function to find out which Account has both "Domain Engagement Status" as 'C' and 'O'.

Please find attached the solution file.

Thanks,

Abhinava

[jagan]

Hi,

Check this

=If(Count({<[Domain  Engagment Status]={'O', 'C'}>} TOTAL <[Sub Domain]> DISTINCT [Domain  Engagment Status]) > 1, 1, 0)

If it not working, attach some sample file.

Regards,

Jagan.