Rewrite a query with the function "switch" from Microsoft Access to Qlikview

[Mattia]

Hi Everyone,

I need to rewrite some queries with the function "switch" from Microsoft Access to Qlikview, but i can have multiple sources and conditions.

Every single query matches and create a new fields, that i want to see in Qlikview.

E.g. This query should create the new field "Tipo_BDG" in Qlikview. In Access the query is the following:

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"

where "TESTATA" is the name of the 1st file (source), "DETTAGLIO" is the name of the 2nd file (another source) and "tipo prestazione", "tipo progetto", "codice prestazione", "ASL/REGIONE" and "regime erogazione" are the name of the fields in the different file/sources.

 So, i have three problems:

1) How can I create a new field by rewriting this query?

2) How can I rewrite the query and the if statement with multiple sources, conditions and fields involved? Can coexist in the same query in Qlikview?

3) How can I rewrite a value that begin with a certain number (in Access, e.g., I can use  asterisk before the number - 0*).

Otherwise, can you suggest me alternative ways to do this (e.g. load inline table)?

Please, help me!!!

Thanx,

Mattia

[Mattia]

I need to rewrite some queries with the function "switch" from Microsoft Access to Qlikview, but i can have multiple sources and conditions.

Every single query matches and create a new fields, that i want to see in Qlikview.

E.g. This query should create the new field "Tipo_BDG" in Qlikview. In Access the query is the following:

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"

where "TESTATA" is the name of the 1st file (source), "DETTAGLIO" is the name of the 2nd file (another source) and "tipo prestazione", "tipo progetto", "codice prestazione", "ASL/REGIONE" and "regime erogazione" are the name of the fields in the different file/sources.

 So, i have three problems:

1) How can I create a new field by rewriting this query?

2) How can I rewrite the query and the if statement with multiple sources, conditions and fields involved? Can coexist in the same query in Qlikview?

3) How can I rewrite a value that begin with a certain number (in Access, e.g., I can use  asterisk before the number - 0*).

Otherwise, can you suggest me alternative ways to do this (e.g. load inline table)?

Please, help me!!!

Thanx,

Mattia

[Stoyan_Terziev · Accepted solution]

Hello Mattia,

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.

[Stoyan_Terziev]

Hello Mattia,

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.

[Stoyan_Terziev]

Hello Mattia,

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.

[Brett_Bleess]

Mattia, did Stoyan's post get you what you needed to move forward?  If so, please be sure to come back to your thread and use the Accept as Solution button on his post to give him credit for the help and let others know things worked.  If you are still in need of further help, please provide a further update on where things stand.

Regards,
Brett