Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

A union in chart's expression. Possible?

I have 2 tables: 1st with companies (CID, manager, type) of 2 types distributed between managers and 2nd table with insurance contracts (InsID, CID, BegDate, EndDate) of 2 types. A type 1 company must have a valid type 1 contract. A type 2 company must have both type 1 and 2 valid contracts. I need to calculate the number of companies for each manager, that violate the rule on a given date (i.e IDs of companies that don't have active type 1 contract and IDs of type 2 companies that don't have active type 2 contract). But I needn't count a company twice if it is of type 2 and have neither type 1 contract nor type 2. In any other db I would make 2 separate queries, UNION them and then COUNT DISTINCT the result GROUP BY manager. But here... I've already made a separate expression for each query, but I need to join them. Can I make a union in expression? Maybe I can unite 2 set analysis operations in a single expression? Thanks in advance!

1 Solution

Accepted Solutions
Not applicable

Re: Re: A union in chart's expression. Possible?

Thanks, I seem to figured out, how to apply it to my solution. Previously I had errors with setting brackets...

Count({($<Type={2}>-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={2}>)+($-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={1}>)}Distinct CID)

4 Replies
MVP & Luminary
MVP & Luminary

Re: A union in chart's expression. Possible?

Perhaps if you post your expressions we can see what you're trying to do and help find an answer. Posting an example document that demonstrates the problem would be even better.


talk is cheap, supply exceeds demand
sudeepkm
Valued Contributor III

Re: A union in chart's expression. Possible?

You can check Set Operators Union. Two set of selections can be union operated.

Not applicable

Re: Re: A union in chart's expression. Possible?

Sure. That's the example of what I have for now...

Not applicable

Re: Re: A union in chart's expression. Possible?

Thanks, I seem to figured out, how to apply it to my solution. Previously I had errors with setting brackets...

Count({($<Type={2}>-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={2}>)+($-<BegDate={"<=$(TargetDate)"},EndDate={">=$(TargetDate)"},Itype={1}>)}Distinct CID)