topic Re: What's the problem? in QlikView

What's the problem?

Mattia — Mon, 28 Oct 2019 15:58:48 GMT

Hi guys,

i've a big problem with the following query, in particular with this script:

If(WildMatch(ApplyMap('KEY_TO_BS_STS21',ApplyMap('DOUBLE_MAPPING',[@10:17],'n/a')),008), If([@4:9]=030357,'PS_RADIO','RADIO'),
If(WildMatch(ApplyMap('KEY_TO_BS_STS21',ApplyMap('DOUBLE_MAPPING',[@10:17],'n/a')),'011'), If([@4:9]=030357,'PS_LAB','LAB')))

as new_field

With these conditions, also the rows n°4 and n°19 it should be valued, respectively with "PS_RADIO" and "PS_LAB".

Why it don't work? It's a problem of data format, syntax (brackets) or something else?

Attached is a sample data file.

Please, someone could show me the right direction?

Thanks,

Mattia

Re: What's the problem?

sunny_talwar — Mon, 28 Oct 2019 16:22:57 GMT

It seems that your 2019_Tariffario_C.xlsx file is not complete... I am not able to make the ApplyMap from KEY_TO_BS_STS21 to work at all... Is this the actual file or did you accidentally deleted some stuff... 69, 54, 36 are not showing up.... within CODICE_PRESTAZIONE column of the Excel file.

Re: What's the problem?

Mattia — Mon, 28 Oct 2019 17:02:29 GMT

Hi Sunny,

please, try with this file that is attached.

Re: What's the problem?

sunny_talwar — Mon, 28 Oct 2019 16:36:22 GMT

Yet again, I don't see 69 within CODICE_PRESTAZIONE field on tab Tariffario or Tariffario (2)... where exactly am I looking?

Re: What's the problem?

Mattia — Mon, 28 Oct 2019 16:42:08 GMT

I think that you're taking the wrong column, because in the script the field is "BS_STS21" while the value 69 is from column "DISCIPLINA".

Re: What's the problem?

sunny_talwar — Mon, 28 Oct 2019 16:48:32 GMT

Let's take an example....

I picked the row CONTATORE_PRESCRIZIONE = 01634821. After I apply -> ApplyMap('DOUBLE_MAPPING',[@10:17],'n/a')... I get 69.... now Next step is the next ApplyMap... which would be something like this -> WildMatch(ApplyMap('KEY_TO_BS_STS21',69) it need to give me 008, correct? Now in order to do this, I need to have 69 here

But I don't see 69 within 2019_Tariffario_C.xlsx or the other file you sent.

Does this make sense?

Re: What's the problem?

Mattia — Mon, 28 Oct 2019 17:01:56 GMT

Ok, the value e.g. 69 is part of column "DISCIPLINA" and you can find it in the script in the table "D":

table "D":
LOAD ...
[@26:27] as DISCIPLINA,

Also, the source is the file in the zip called sample_data_D

Re: What's the problem?

sunny_talwar — Mon, 28 Oct 2019 17:29:18 GMT

This make sense... I think problem is that you have multiple matches within your mapping table... for example... when I run this

DOUBLE_MAPPING: LOAD [@10:17] as CONTATORE_PRESCRIZIONE, [@29:33] as CODICE_PRESTAZIONE FROM [Sample data_D.txt] (fix, utf8, no labels);

For a given CONTATORE_PRESCRIZIONE, I see multiple CODICE_PRESTAZIONE

But mapping is only able to map it to the first instance... which is picking up CODICE_PRESTAZIONE of 897B7. This in the end becomes 019. But you seem to want it to pick 8828, so that it can become 008. But what is the logic for picking 8828 instead of 897B7?

Re: What's the problem?

Mattia — Mon, 28 Oct 2019 17:56:12 GMT

Actually, for every CONTATORE", all the records with different "CODICE PRESTAZIONE" must be picked and valued in the column "TIPO". Anyway, it's perfectly normal that every CONTATORE i've multiple CODICE PRESTAZIONE.

e.g. 897B7 must have "PS_AMB" in the column "TIPO" (now, the query is partial hidden because i think that the problem is not in the following script: If(Len(Trim([@10:17]))>0, If([@4:9]=030357,'PS_AMB','AMB').

The strange thinG is that if i insert a inline table, the query works perfectly (see the attached file).

The output that i want to obtain is the following: if the field BS_STS21 = 008 and if the field CODICE_PRESIDIO = 030357, the record must show PS_RADIO, instead RADIO; if the field BS_STS21 = 011 and if the field CODICE_PRESIDIO = 030357, the record must show PS_LAB, instead LAB. All the records must have a value in the column TIPO.