Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikview users,
please, i need your help to solve the following problem.
I need to create a table with a lot of fields, where some of this are already uploaded in other tables and some of this it should be created from scratch as a results of multiple expressions/conditions, e.g. if statements, switch, etc. (like an Access query).
I'm new on Qlikview so:
1) how and where can I write these expressions and create new fields as a results of these conditions that involve some of the fields already uploaded?
2) how can I manage multiple sources in the same expression? Because the condition involve multiple fields in multiple files/sources.
Thanks to everybody that i'll bring me in the wright direction.
Mattia
Hello Mattia,
I lost my reply few times for some reason...
To your original question:
I will go 3,2,1
3) I think you are asking for the WildMatch(Attribute, Value) function.
To use your example, it works like (WildMatch([tipo prestazione], '*J*')
Check it out here: https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Condi...
2) You need to 'mix' the sources together. You can either 'Join' or Map the two source ( and ) .
I'd recommend you use the mapping function you can check here: https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...
3) First is first - Indentation (I suspect your query has a missing ')' for the switch btw):
If this is all one big switch statement, with output of 1 attribute, then:
Switch(
[TESTATA]![tipo prestazione] Like "J";"J";
[TESTATA]![tipo PROGETTO] Like "K";"K";
[TESTATA]![tipo prestazione] Like "S";
IIf(([DETTAGLIO]![codice prestazione] Like "presta1") Or ([DETTAGLIO]![codice prestazione] Like "presta2");"SCREENING";"BDG");
[TESTATA]![Regime erogazione]="7";"STRANIERI";
[TESTATA]![Regime erogazione]="8";"BDG";
[TESTATA]![ASL/REGIONE] Like "0*"
Or [TESTATA]![ASL/REGIONE] Like "1*"
Or [TESTATA]![ASL/REGIONE] Like "2*"
Or [TESTATA]![ASL/REGIONE] Like "888";"EXTRAREG"
)
Simplest of all methods would be something like:
TESTATA:
LOAD
If(WildMatch([tipo prestazione], '*J*'), 'J',
If(WildMatch([tipo PROGETTO], '*K*'), 'K',
If(WildMatch([tipo prestazione], '*S*'), 'S',
If(WildMatch(ApplyMap('MapDETTAGLIO', KeyFieldBetweenTESTATAandDETTAGLIO, 'n/a'), '*presta1*', '*presta2*'), 'SCREENING',
IF([Regime erogazione]='7', 'STRANIERI',
IF([Regime erogazione]='8', 'BDG',
IF(WildMatch([ASL/REGIONE], '0*', '1*', '2*', '2*' '888'), 'EXTRAREG'
)
)
)
)
)
)
) As ThisOneFieldAttribute
FROM TESTATA
;
But I suspect you are posting a query for multiple attributes so it looks like this:
TESTATA:
LOAD
If(WildMatch([tipo prestazione], '*J*'), 'J') as Attr1,
If(WildMatch([tipo PROGETTO], '*K*'), 'K') as Attr2,
If(WildMatch([tipo prestazione], '*S*'), 'S') as Attr3,
If(WildMatch(ApplyMap('MapDETTAGLIO', KeyFieldBetweenTESTATAandDETTAGLIO, 'n/a'), '*presta1*', '*presta2*'), 'SCREENING') as Attr4,
IF([Regime erogazione]='7', 'STRANIERI') as Attr5,
IF([Regime erogazione]='8', 'BDG') as Attr6,
IF(WildMatch([ASL/REGIONE], '0*', '1*', '2*', '2*' '888'), 'EXTRAREG') as Attr7
FROM TESTATA
;
I hope this helps!
Kind regards,
S.T.