Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Request to resolve my challenging work.

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 DomainFunctional AreaAccountDomain Engagment Status
PayerMembershipKaiserC
PayerMembershipUnited Health GroupC
PayerMembershipHumana CarePlusC
PayerBenefits ManagementHumana CarePlusC
PayerProvider ManagementUnited Health GroupC
PayerProvider ManagementHumana CarePlusC
PayerMedical ManagementHumana CarePlusO
PayerClaim AdjudicationHumana CarePlusC
PayerFinanceKaiserC
PayerFinanceHumana CarePlusC
PayerUnderwriting & ActuariesHumana CarePlusO
PayerCustomer ServiceHumana CarePlusO
PayerCustomer ServiceNEHEN and Rx GatewayC
PayerSales & MarketingKaiserC
PayerEDI (HL7/X12)KaiserC
PayerEDI (HL7/X12)Humana CarePlusC
PayerEDI (HL7/X12)NEHEN and Rx GatewayC
PayerClaim Fraud DetectionTruvenC
PayerClaim AuditsTruvenC
PayerHealth PlansTruvenC
PayerAnalyticsTruvenC
Life SciencesResearch & DevelopmentPfizerO
Life SciencesDrug DiscoveryPfizerO
Life SciencesClinical TrialsPfizerO
Life SciencesMedical InfoPfizerO
Life SciencesQuality & ManufacturingPfizerO
Life SciencesQuality & ManufacturingGlenmark GenericsC
Life SciencesQuality & ManufacturingPacific BiosciencesO
Life SciencesSales & MarketingPfizerO
Life SciencesLegal / CorporatePfizerO

Thanks for you reply.

10 Replies
Highlighted
Champion
Champion

Prajeesh Kumar

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

Vikas

Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Not applicable

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.

Highlighted
Not applicable

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.

Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Partner
Partner

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

Highlighted
Not applicable

Hi Jagan,

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

Highlighted
Not applicable

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

Highlighted
MVP & Luminary
MVP & Luminary

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.