Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

List of 3 (comma separated) tags split into 3 fields

Hey,

I have a source file that has a field containing tags. I know that its always 3. The field content looks something like this:

Tags
Building a, unit 4, aCategory

I have split them into fields Tag 1, Tag 2, Tag 3. (As I know its always 3) I used something like this:

subfield([Tags], ',', 1) as tag1,

subfield([Tags], ',', 2) as tag2,

subfield([Tags], ',', 2) as tag3,

The problem that I know have is that each of the tag is like a describing attribute and in my model a hierarchy leaf member. So I would love to do the following:

subfield([Tags], ',', 1) as Building,

subfield([Tags], ',', 2) as Unit,

subfield([Tags], ',', 2) as Category,

However this doesn't work as the ERP system I get this concatenated string from does sometimes mix the sequence of tags so it can't be  defined by the order of entry which defines the building, which the unit and which substring is the category. So sometimes it shows like

Tags
Building a, aCategory, unit 4

And with this my mapping above that hardcodes the first tag to be the Building Name won't work. I think I need to create a logic in the script that checks the value of each tag (1,2 and 3) against a list of possible values.

List of Buildings

Building a

Building b
Building c
...

Like against a list of all existing buildings. If it matches one of the known building names it can be feed to the output building field. The same would need to be done for Unit and Category.

So my question is now how can I build a script logic that does something like this:

<pseudo code>

when tag 1 in List of Buildings then value of tag 1 else when tag 2 in List of Building then value of tag 2 else when tag 3 in List of Buildings then value of tag 3 else null

</pseudo code>

I am sure there are smarter way than what I could think of with my limited knowledge on Qlik Sense Scripting!

1 Reply

Re: List of 3 (comma separated) tags split into 3 fields

It's not hard. You can use the exists() function. FIrst load up all buildings in a temp file

TempBuildings:

LOAD Building FROM ListOfBuildingsFile...

;

In the load:

if(exists(Building, subfield(Tags, ';', 1)), subfield(Tags, ';', 1)

,if(exists(Building, subfield(Tags, ';', 2)), subfield(Tags, ';', 2)

,if(exists(Building, subfield(Tags, ';', 3)), subfield(Tags, ';', 3)

))) as Building

After the load, drop the TempBuildings.

DROP TABLE TempBuildings;

Just clone for Category and Unit.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com


Community Browser