Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Mattia
Creator II
Creator II

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

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

Labels (3)
1 Reply
Stoyan_Terziev
Partner
Partner

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.