Exclude values to another group

[#1]

Hello you all,

Got a situation where I want te exclude some values from a field called Gegevens into another field, but can't get the syntax right. My code so far:

Load
IF(Trim(Gegevens)= 'H900: P/dgdl. dagbest. VG-licht (VG1-VG4)', 'H900: P/dgdl. dagbest. VG-licht (VG1-VG4)',
IF(Trim(Gegevens)= 'H901: P/dgdl. dagbest. VG-mid. (VG5,VG6)', 'H901: P/dgdl. dagbest. VG-mid. (VG5,VG6)',
IF(Trim(Gegevens)= 'H902: P/dgdl. dagbest. VG-zwaar (VG7)', 'H902: P/dgdl. dagbest. VG-zwaar (VG7)'))) as Gegevens2,
Gegevens as ZZP

Now I want to get two different fields: Gegevens2 and ZZP. My code does this BUT the 3 values mentioned (H900, H901, H901) are now included in both fields. I want those 3 values into field Gegeven2 and OUT of field ZZP.

Can anyone help me?

Regards,

Sanchez

[#2 · Accepted solution]

Hello Richard,

I shortend your string a little bit, but the logic should be visible. You need too statements, one for each field:

IF(Trim(Gegevens)= 'H900' OR Trim(Gegevens)= 'H901' OR Trim(Gegevens)= 'H902', Trim(Gegevens), null()) AS Gegevens2,
IF(Trim(Gegevens)<> 'H900' AND Trim(Gegevens)<> 'H901' AND Trim(Gegevens)<> 'H902', Trim(Gegevens), null()) AS ZZP,

BTW: I would declare some variables like SET vH900 = 'H900: P/dgdl. dagbest. VG-licht (VG1-VG4)' for easier coding.

Regards, Roland

[#3]

Hello Roland,

Thx for the nice code, it works like I want it to. Now the next thing is that I have one total field which now sums up everything in the table, but with the excluding of those 3 values I want to get 2 total fields.

Like total1 (sum of the zzp field) and total2 (sum of the gegevens2 field)

Is this also possible?

[#4]

Hi Richard,

glad to help you, don't forget to mark my answer above as solution.

To your next question: this should be possible. I think with an if()-Statement would do the Job similar to (sum (if ZZP <> null(), Value, 0)). But for this I need more details.

RR

[#5]

My formula for the second question = if (ZZP <> null(), sum(Totaal), 0) But I don't get any data without selecting anyting. I do not want to click on anything so the formule must be made in the script instead of in a pivot table.

The table is like:

Gegevens Total
Z300 50
Z500 25
H900 5
H901 5
H902 10
....
....
With the formulas given by you, I've splitted the field 'Gegevens' into 2 fields (ZZP & Gegevens2) because H900 H901 & H902 must be filtered out. The Total field stil sums up everything up. So the total sum = 95.

I want a total sum of 75 & a second total sum of 20

Regards. Richard

[#6]

Hi Richard,

take care of the order of the if() and the sum(). Your expression does anything else, please compare it with mine. There you can see that the IF() is operating within the sum()-function as a filter. Which means only rows passing the if()-filter will be summed up (are a valid summand of sum()).

Edit:

I would use the formular in the expression of the chart or pivot. Its easy to use.

Got it?

Roland

[#7]

Hi Roland,

No, I cannot get it working. I use this formular:

(sum(if (ZZP <> null(), (Totaal), 0))) /(365) * (334)

The outcome is +/- 25 times higher than it should be.... What could be wrong?

Regards, Richard

[#8]

Hi Richard,

hard to guess. Are you able to post a exam application? I am afraid thats the only way to look behind the curtain.

RR

[#9]

That would be a problem but I think I know what's wrong. Got a $Syn 6 table.

Trying to make an alias but that breaks the links between some relevant fileds and so it does not work. It seems like the only solution is to join some fields in a table but that's something new for me... inner/left/right drop table/fields... ....pffff hard because it's not clear SQL!

[#10]

It isn't so hard as you expect it. The main purpose is to create a reasonable data modell. I prefer a star schema with one factstable in the center and dimensions surrounding the facts.

A Syn-Table must not be wrong in every case. Linking tables together is done in QV with same fieldnames in different QV-tables. For the first time this can be misleading when you think about similar columnnames in different SQL-tables. In SQL it is quit clear while Qlikview does not qualify unless being told so. If you have designed a correct combined key (with five or six fields) i.e. for referencing a master-detail relationship the resulting SynTable will be also correct. The QV-Joins are slightly different as in SQL (in fact it is a kind of merging tables together), but they are more a technical issue. If the design of your data modell is correct, it should work independent of the QV-Joining or even not QV-joining and keeping linked tables.

RR