How to create new fields as a result of expressions that involves multiple fields and sources

[Mattia]

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

[Stoyan_Terziev]

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.