How to extract from PATH with /Level1/Level2/Level3/Level5 in Cognos expression

Check list of all posts

Solution 1:


Step 1:  Determine LEVEL
case INSTR([PATH], '/', 1, 2)
   when 0 then 1
   else
   (case INSTR([PATH], '/', 1, 3)
      when 0 then 2
      else
      (case INSTR([PATH], '/', 1, 4)
         when 0 then 3
         else
         (case INSTR([PATH], '/', 1, 5)
            when 0 then 4
            else
            (case INSTR([PATH], '/', 1, 6)
               when 0 then 5
               else
               (case INSTR([PATH], '/', 1, 7)
                  when 0 then 6
                  else
                  (case INSTR([PATH], '/', 1, 8)
                     when 0 then 7
                  end)
               end)
            end)
         end)
      end)
   end)
End

Step 2:  Extract LEVEL x
CASE WHEN [LEVEL] = x  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, x)+1)
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, x)+1,  INSTR([PATH], '/', 1, x+1) - INSTR([PATH], '/', 1, x) - 1)
END


Samples
Level1
CASE WHEN [LEVEL] = 1  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 1)+1)
ELSE 
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 1)+1, INSTR([PATH], '/', 1, 2) - INSTR([PATH], '/', 1, 1) - 1)
END
Level2
CASE WHEN [LEVEL] = 2  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 2)+1)
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 2)+1, INSTR([PATH], '/', 1, 3) - INSTR([PATH], '/', 1, 2) - 1)
END
Level3
CASE WHEN [LEVEL] = 3  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 3)+1)
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 3)+1, INSTR([PATH], '/', 1, 4) - INSTR([PATH], '/', 1, 3) - 1)
END
Level4
CASE WHEN [LEVEL] = 4  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 4)+1)
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 4)+1,  INSTR([PATH], '/', 1, 5) - INSTR([PATH], '/', 1, 4) - 1)
END
Level5
CASE WHEN [LEVEL] = 5  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 5)+1)
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 5)+1, INSTR([PATH], '/', 1, 6) - INSTR([PATH], '/', 1, 5) - 1)
END




Solution 2:


Extract LEVEL x
CASE WHEN INSTR([PATH], '/', 1, x) > 0 AND INSTR([PATH], '/', 1, x+1) = 0  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, x)+1) 
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, x)+1, INSTR([PATH], '/', 1, 3) - INSTR([PATH], '/', 1, x) - 1)
END


Sample

Level 2
CASE WHEN INSTR([PATH], '/', 1, 2) > 0 AND INSTR([PATH], '/', 1, 3) = 0  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 2)+1) 
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 2)+1, INSTR([PATH], '/', 1, 3) - INSTR([PATH], '/', 1, 2) - 1)
END


Level 3
CASE WHEN INSTR([PATH], '/', 1, 3) > 0 AND INSTR([PATH], '/', 1, 4) = 0  THEN
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 3)+1) 
ELSE
 SUBSTRING([PATH],INSTR([PATH], '/', 1, 3)+1, INSTR([PATH], '/', 1, 4) - INSTR([PATH], '/', 1, 3) - 1)
END