Hello,
I have a project where I receive data from a third party in an excel format. I don't have the ability to modify, change, or filter the data from the third part prior to receiving the excel file. The excel file is stored on my local computer for now. I have Qlik Sense loading the data at which point I have a script to create a new field with concatenated data from 4 other fields in the same table. From this concatenated data I need to find specific information or combinations of specific information to identify what specific product each data record represents. How can I use a script that filter through the concatenated field after initial load and update the "Product" field, that does already exist in the table with the right information. Here are some examples of the concatenated field content and what I need it to change the Produce field to. The data in the longer lines below such as Latex line are slightly different every time as they include the railcar number for that specific record.
I'm looking for something along the lines of If Column A contains *KaoMax* then update column b of that record to "KaoMax".
I can do this with SQL statements in ms access or similar platform but since Qlik can't update the excel data source then the way I would normally do it doesn't work. i don't want to have a manual step in between where I have to clean up the excel worksheet before the information is usable in Qlik. Ultimately, when I have a working proof of concept for this report, the data will be loaded into our data lake but for now I'm trying to determine the best way to update the data table after load.
Any help would be greatly appreciated.
Example:
Concatenated Field Product field after update
ADM 2046210 Clinton 444 Industrial ADM Starch
Archer Daniels Midland CO 2046210 Corn Starch ADM Starch
LATEX (LIQUID RUBBER), ACRONAL S 504 NA 1 CSR PAPER BASF.COM DBCX 200438 GAL SA S504
KAOMAX AK SLURRY GATX 64929 GAL CP= 14507 KaoMax AK
KAOMAX AK SLURRY KaoMax AK
