Werte in einem Datenfeld extrahieren

[Buri]

Hallo, ich habe eine Frage. Wie kann ich die Werte, die mit A... anfangen in einem Datenfeld in einer neuen Spalte extrahieren? Beispiel: 

{"A21390315433" : {"match" : " " , "nameDE" :  null, "nameEn": null}, "A21390898265" : {"match" : " " , "nameDE" :  null, "nameEn": null},  "A21391238265" : {"match" : " " , "nameDE" :  null, "nameEn": null}}

Ich benötige die Werte mit A.... in einer neuen Spalte.

Vielen Dank.

[HeshamKhja1]

Hi,

Sorry I will reply in English.

Did you already load the data in Qlik as a tabular format?

If yes, then you can do as follows:

LOAD
   OldColumn ,
   If(WildMatch(OldColumn, 'A*'), OldColumn) AS NewColumn
FROM XXXX;

The code check if values in OldColumn starts with 'A..', if yes then the value of OldColumn will be written in NewColumn, else null.

[Buri]

Hello, thank you very much for your reply. I've loaded the data as a tabular format. I added the code, unfortunately it didn't work. If I add 'A*', I don't get a result. But if I add '*A*', I get all the results. But I only want to find the values that start with A

[HeshamKhja1]

I can't see why it won't work. Unless the first letter is something other than A. Then you can try '$A*' '?A*'.

Can you share a small sample to see how data is showing in Qlik?

[Buri]

that's my code:

LIB CONNECT TO 'AVR_recovery_claim';

LOAD claim_id, 

master_ecu, 

hardware_part_number, 

bci, 

ecu, 

diagnosis_start_time, 

mileage, 

version, 

version_id, 

old_software,

    If(WildMatch(old_software, 'A*'), old_software) AS old_software_NEW,

new_software, 

diagnosis_update_error, 

part_es1, 

part_es2, 

bug_software, 

fix_software, 

flash_case;

[recovery_claim_diagnosis_data]:

SELECT "claim_id",

"master_ecu",

"hardware_part_number",

"bci",

"ecu",

"diagnosis_start_time",

"mileage",

"version",

"version_id",

"old_software",

"new_software",

"diagnosis_update_error",

"part_es1",

"part_es2",

"bug_software",

"fix_software",

"flash_case"

FROM "recovery_claim"."recovery_claim_diagnosis_data"

WHERE date("diagnosis_start_time") >= '2022-01-01 00:00:00.000';

[HeshamKhja1]

Aha, the JSON is still loaded as is. I thought you already got it into Qlik as tabular format.

I expect the code below to achieve what you want:

If(WildMatch(OldColumn, '??A*'), OldColumn) AS NewColumn

The ?? tells Qlik that A comes after two characters.

[Buri]

It worked, but not as desired..

In the new column, only the values with "A..." should appear. So:

[HeshamKhja1]

We can try using string functions, but I think the best way to address your issue is by reading the JSON file properly. Might take longer, but you'll end up with proper knowledge.

Check this post that seems to be the latest on the topic:

https://community.qlik.com/t5/Member-Articles/How-to-read-json-files-with-Qlik-Sense/ta-p/2120598 

Of course, feel free to search the topic Qlik Sense read JSON. You might find more straight forward solutions for your case.