Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
vikasmahajan

Prajeesh Kumar

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

Vikas

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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.

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

Hi Jagan,

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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.