topic Re: Merging two fields in one dimension in QlikView

Merging two fields in one dimension

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

I have two queries that join on ID, but put some different information so to avoid synthetic keys I have two "Specialist" fields. I want to combine the two formulas I have into one to get a total column but my table currently has Specialist1 as the dimension. The issue is the second formula would count IDs per Specialist 2. These are the same people, but again it makes more sense to join on ID. Do you know a way I could combine the formulas and somehow have the correct answer for my dimension?

My formula would look like COUNT({<Formula1 set analysis> + <Formula2 set analysis>}DISTINCT ID) since some of the items overlap and this gets me the correct answer. It works in another table where I dont have this issue with the dimensions but I cant get it to work here. The numbers in the bottom table would be correct, but even it it was just straight addition I'm trying to get the two specialist columns into one, or a workaround.

ex:

Specialist1	Count({<formula1 set analysis>} DISTINCT ID)
Bill	3
Bob	1
Joe	4
Specialist2	Count({<formula2 set analysis>} DISTINCT ID)
Bill	5
Bob	2
Joe	3
	Combined set analysis
Bill	7
Bob	2
Joe	4

Re: Merging two fields in one dimension

vishsaggi — Mon, 15 Oct 2018 20:16:45 GMT

Do you have a sample data or a qvw file we we can work with?

Re: Merging two fields in one dimension

greend21 — Tue, 16 Oct 2018 12:40:18 GMT

Unfortunately no. The example is the best I could come up with. Even if it was just Count(Distinct ID) I have the issue of Specialist1 and Specialist2 dimensions.

Re: Merging two fields in one dimension

jensmunnichs — Tue, 16 Oct 2018 14:30:28 GMT

Kind of a hard one to solve because you can't share your real data, but I'll give it a shot.

Would it be possible to solve this in script? First option:

Table1:

LOAD * Inline [

ID, Specialist1

1, Bill

2, Bill

3, Bill

4, Bob

5, Joe

6, Joe

7, Joe

8, Joe];

Table2:

LOAD * Inline [

ID, Specialist2

3, Bill

9, Bill

10, Bill

11, Bill

12, Bill

13, Bob

4, Bob

5, Joe

6, Joe

7, Joe];

Table3:

LOAD ID,

Specialist1 as Specialist

Resident Table1;

LOAD ID,

Specialist2 as Specialist

Resident Table2;

What I did:

- Load sample data

- Load ID (and any other common fields needed for this expression) and Specialist1 as Specialist

- Same thing again, but with Specialist2

- These concatenate into one table with Specialist, ID, any other common fields

- Dimension in table: Specialist

- Expression in table: COUNT({<set analysis>} DISTINCT ID)

Second option:

If you have a lot of data, you probably don't want to add another table containing millions of records to the data model. In that case you could do the calculation in script as well. However, you can't use set analysis in script, so if you need that you'd need to work with an IF (there are probably better solutions but I'm not sure what they are, still fairly new to Qlikview myself).

Anyway for a simple Count just add this to the script above:

Table4:

LOAD Specialist,

Count(DISTINCT ID) as Count

Resident Table3

Group by Specialist;

Drop table Table3;

The table then simply has Specialist as dimension, and Sum([Count]) as expression. Table4 has way less records than Table3 assuming a lot of data in your source.

Problem is, this creates a data island, which you probably don't want either. You can connect it back to the data model by adding 'Specialist as Specialist1' to the load in Table4 (as well as Specialist on its own), but this would create a 'bad' association, so you'd have to add set analysis like 1<Specialist1=$::Specialist1> or something like that to any other expression in the app, as otherwise you'd get wrong answers when users make a selection.

Sorry for the wall of text. This probably isn't the full answer to your problem, but I hope it's of some use to you anyway.

Re: Merging two fields in one dimension

greend21 — Tue, 16 Oct 2018 15:12:15 GMT

This interesting but doesn't look like it would work. There will be hundreds of thousands to millions of records eventually. Plus, we are joing on ID's so if I make both fields the same name I will have a synthetic key.

Re: Merging two fields in one dimension

jensmunnichs — Wed, 17 Oct 2018 07:09:51 GMT

I thought the only fields in your data model would be 'Specialist1' and 'Specialist2', so 'Specialist' without a number would be unique.

Nevertheless, I think I may have found a solution, and it's a lot simpler than I thought. Have a look at the attached file.

It seems like you can just use a calculated dimension to get a list of all available specialists, using =If(isnull(Specialist1),Specialist2,Specialist1)

I don't know how well this would do performance wise though, since I think the If would have to be calculated for every row...