What's the problem?

[Mattia]

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

[sunny_talwar]

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.  

[Mattia]

Hi Sunny,

please, try with this file that is attached.

[sunny_talwar]

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

[Mattia]

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".

[sunny_talwar]

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?

[Mattia]

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

[sunny_talwar]

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?

[Mattia]

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.