topic Re: Request to resolve my challenging work. in QlikView

Request to resolve my challenging work.

Mon, 30 Dec 2013 06:44:07 GMT

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.

Re: Request to resolve my challenging work.

vikasmahajan — Mon, 30 Dec 2013 06:49:43 GMT

Prajeesh Kumar

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

Vikas

Re: Request to resolve my challenging work.

jagan — Mon, 30 Dec 2013 06:59:28 GMT

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.

Re: Request to resolve my challenging work.

Mon, 30 Dec 2013 07:01:22 GMT

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.

Re: Request to resolve my challenging work.

Mon, 30 Dec 2013 07:03:24 GMT

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.

Re: Request to resolve my challenging work.

jagan — Mon, 30 Dec 2013 07:04:56 GMT

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.

Re: Request to resolve my challenging work.

NareshGuntur — Mon, 30 Dec 2013 07:07:51 GMT

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

Re: Request to resolve my challenging work.

Mon, 30 Dec 2013 07:57:23 GMT

Hi Jagan,

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

Re: Request to resolve my challenging work.

Mon, 30 Dec 2013 08:16:03 GMT

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

Re: Request to resolve my challenging work.

jagan — Mon, 30 Dec 2013 08:33:53 GMT

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.

Re: Request to resolve my challenging work.

suresh_rawat — Mon, 30 Dec 2013 08:39:57 GMT

HI Prajeesh,

Kindly refer the attached image and qvw file.

Hope it will work for you.

Regards

Suresh Rawat