Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

new_guy
New Contributor III

Sub routines

Hi guys,

I have to deal with a column which concists of serveral merged information. For example the column is called "SnapshotData" and a value is like "Id-1212233#No-23434#Adress-blablaabal#...." (like 20 different information merged into one field). 

My goal is to split the field into different columns and values, like:

column id, value 1212233,

column No, value 23434 and so on. 

Since there are so many informtion within this field i wanted to temporarily pass all the information into a table and give this table to a subroutine afterwards. 

This subroutine then should seperate the values with something like "for...next" or "for....each".  This is what I came up with so far.

 

 

 

SUB Snapshot_fieldnames(temp_Table)
 	For i=0 TO $(some sort of variable)+1 
    	   Let Snapshot_Content = FieldValue(SnapshotData,1);
    	   let tablename = '$(temp_Table)';

    	   final_table:
    	   Load
        	'$(Snapshot_Content)' as fieldnames
           Autogenerate 1;
       
        Next i
End Sub

 

 

 

 

First thing I challenged is that my variable "Snapshot_Content" is null everytime I try it. The variable "tablename" is filled with my previous generated tablename.  

Any suggestions how to do this? 

 

Greetings

new_guys

1 Reply
Partner
Partner

Re: Sub routines

A couple things:

 

FieldValue takes a quoted string.

Let Snapshot_Content = FieldValue('SnapshotData',1);

 

temp_Table looks right. Make sure you are calling the sub using a quoted string as well: 

Call Snapshot_fieldnames('Fact');

 

Although from your snippet, I don't see the purpose of the variable. Especially since anywhere you intend to use '$(tablename)', you can use '$(temp_Table)'.

 

Lastly, based on your example I think you could do this using Subfield and a Generic Load

Data:
Load
    ColumnId,
    Subfield(ColumnOrg,'-',1) as ColumnName,
    Subfield(ColumnOrg,'-',2) as ColumnValue
;
Load
    RowNo() as ColumnId,
    Subfield(SnapshotData,'#') as ColumnOrg //creates a row for each column-value
From
[lib://.....];

ParsedData:
Generic Load
    ColumnId,
    ColumnName,
    ColumnValue
Resident
    Data;

Drop Table Data;

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

    Let vTableName = TableName($(vTableNo)) ;

    If Subfield(vTableName,'.',1)='ParsedData' Then

        Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

    End If

Next vTableNo


CleanData:
Load distinct ColumnId From ParsedData;


For each vTableName in $(vListOfTables)

    Left Join (CleanData) Load * Resident [$(vTableName)];

    Drop Table [$(vTableName)];

Next vTableName

Drop Table ParsedData;

 

Blog: WhereClause   Twitter: @treysmithdev