User Defined Functions in Qliksense

[Sanket_Sharma]

Hi all,

I would like to know whether it is possible that we have have user defined functions in Qlik sense. As we have in programming languages. The task that i aim to achieve is to apply loop on a certain column names so that i can increase their time based on further conditions. 

Query is regarding whether can we create a user define function or not. If yes the syntax would help a lot.

Attaching Requirement in detail. 

Thanks 

@johnw @sunny_talwar 

[marcus_sommer]

User defined functions are not directly possible but with parametrized variables you are quite close to this kind of functionality:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/dolla...

- Marcus

[Sanket_Sharma]

Hi @marcus_sommer ,

Can Loop be applied using dollar sign, also i will be calling this function again and again so that particular column that will be updated, will it retain its value.

Thanks 

[tresesco]

@Sanket_Sharma ,

Initial thought after going through your requirement - you might not need a UD function, and probably similar could be achieved using qlik functions itself. If you could create a sample app with inline data and may be explain your use case there, we could have a look and give a try to help you.

[marcus_sommer · Accepted solution]

It's not quite clear for me what you mean with "Can Loop be applied using dollar sign ..." ? Where do you want to use it?

(Parametrized) variables are mainly a text-replace of other native functions/calculations - separate loops independent to it's dimensionality aren't possible neither in script nor in the UI. A loop needs in general a control- or a load-statement within the script (of course there are a few exceptions).

In regard to your requirement-doc above I'm not sure that you need a customized functions at all. Qlik is designed to create everything needed within a suitable datamodel and not to use "classical" sql/programming logic within it.

In your case I think I would try to use a master-calendar and a master-timetable in which it's quite easily to define within one or maybe several flags which days/hours are working ones and which not. Depending on the data and the complexity you might even need an extra specialized created calendar with hours + minutes and/or maybe multiple ones if there are differences between countries, shifts, factories and so on.

More to it could you find here: How-to-use-Master-Calendar-and-Date-Values 

- Marcus

[gabriel-spiteri]

@marcus_sommer @Sanket_Sharma 

I have run into a similar situation ...  

Map_Partner:
Mapping LOAD * INLINE [
  lookup, partner
  'y', 'YYYYY',
  'y/b', 'YYYBBB'
];

// Subroutine

Sub CleanPartnerName (brand)
    // Extract the partner from between the two pipelines of the idorder field 
    Let partnerName = trim(SubField(lower(brand), '|', 2));
    
    // Remove anything after a "\"
    partnerName = SubField(partnerName,'\',1);
    // Remove anything after "&amp"
    partnerName = SubField(partnerName,'&amp',1);
    // Remove anything after a "?"
    partnerName = SubField(partnerName,'?',1);
    // Remove trailing "/"
	if((Len(partnerName) - 1) = Len(SubField(partnerName, '/', 1))) then
    	partnerName = SubField(partnerName, '/', 1);
    end if
    
    brand = ApplyMap('Map_Partner', partnerName, 'Untracked Leads')
        
End Sub

Call CleanPartnerName ('x|y|z');


// Dollar expansion

SET extractPartnerName = "SubField(lower($1), '|', 2)";
SET removeBackSlash = "SubField($(extractPartnerName($1)), '\', 1)";
SET removeAmpersand = "SubField($(removeBackSlash($1)),'&amp',1)";
SET removeQuestionMark = "SubField($(removeAmpersand($1)),'?',1)";
SET removeTrailingFowardSlash = "If((Len($(removeQuestionMark($1))) - 1) = Len(SubField($(removeQuestionMark($1)), '/', 1)), SubField($(removeQuestionMark($1)), '/', 1), $(removeQuestionMark($1)))";
SET cleanPartnerName = "Trim($(removeTrailingFowardSlash($1)))";

FinalData:
LOAD 
ID, 
ApplyMap('Map_Partner', $(cleanPartnerName(brand)), 'Untracked Leads') as partner_name
Inline [
ID, brand
123,x|y|x
145,x|y/b|x
156,x|y/|x
179,x|z\|x
];

I was able to achieve the intended result using the dollar expansion but as you can see it gets very untidy. Is there any better approach I am missing here? The logic in the sub routine is much more clear and self-explained. 

[marcus_sommer]

You may not need all those variables because you could nest multiple subfield() like:

subfield(subfield(subfield(F, 'X', 1), 'y', 1), 'z', 1)

Another approach could be to split each string-part directly within loads - within any (inside or outside) loop-logics but I'm not sure that this would be easier in this case.

- Marcus