1 Reply Latest reply: Aug 14, 2015 8:00 PM by Rob Wunderlich RSS

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

    Anselm Drescher



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


      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


      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!