topic Extract Data from within a Cell when the source is an excel sheet in QlikView

Extract Data from within a Cell when the source is an excel sheet

pandreozzi — Wed, 30 Oct 2019 13:55:46 GMT

I have an excel spreadsheet. In this sheet as expected there are multiple cells. In some of my cells there is data that is related to another cell. I want to read that data and form a relationship with another cell that contains the same date.

Example:

Cell 1 contains the following:
vsid1 firewall name (this is not separated by any commas or other point)

Cell 2 contains the following
vsid 1 ip address interface name

i want to read vsid 1 in both cell1 and cell2 to form the relationship and the result needs to be the firewall name,ip addresses and interface name in 1 output

Re: Extract Data from within a Cell when the source is an excel sheet

pradeep_s — Fri, 01 Nov 2019 21:04:02 GMT

You can split it based on Space (I can see space in the lines you wrote about values of Cell1 and Cell2)

Below tables will have Split Values.

Table1:

Load Subfield(Cell1,' ') as Cell1Values

Resident

SpreadSheet;

Table2:

Load Subfield(Cell2, ' ' )as Cell2Values

Resident

SpreadSheet;

Next Steps:

Inner Join Both the tables . The Common Value will be filtered out. (Another Approach:Even you can do lookup)

To the Resultant table, Join the remaining Values of Table1 and Table2 (Concat - ideally from Cell1 and Cell2).

you have to run this code in loop to process all the rows of spreadsheet.