topic Re: Can create a function ? in QlikView

Can create a function ?

Fri, 29 May 2015 13:17:35 GMT

Hi,

I try to know how I can call a function.

ex:

I make a table:

LOAD

FROM

Documents

and now I want to use this table in each other table I have without copy/past. Until now I put this table into a variable and I wonder if is it work.

Regards

Re: Can create a function ?

Anonymous — Fri, 29 May 2015 13:21:22 GMT

Not sure that I understand your question. Could you clarify ?

Re: Can create a function ?

Fri, 29 May 2015 13:23:55 GMT

Actually I try to show all error that my apply can make. And I have this:

SET vError = FIRST 1 CONCATENATE(ERROR) LOAD

COUNTRY_CODE,

'$(NbRows)' as NB_ROWS,

'$(ScriptErrorCount)' as ERROR_SEQ_NUMBER,

'$(#ScriptError)' as ERROR_ID,

'$(ScriptError)' as ERROR_NAME,

'$(ScriptErrorDetails)' as ERROR_DETAIL,

'$(ScriptErrorList)' as ERROR_LIST,

now() as ERROR_DATE

resident COUNTRY;

I want to put this script into every table I have to check and put into a table. And i wonder if this way to work is correct or there's other way to make it.

Re: Can create a function ?

alexandros17 — Fri, 29 May 2015 13:24:09 GMT

Simply use an alias as a name for the table:

MyTable:

LOAD

FROM

Documents;

Now you can use MyTable for example:

Load * resident MyTable;

Re: Can create a function ?

undergrinder — Fri, 29 May 2015 13:29:03 GMT

Hi!

If you specify your problem, then we may help you.

You can create a function with a VB like script.

eg:

Sub function ()

LOAD

FROM

Documents

end sub

call function

In Chart and function guide there is some example (https://help.qlik.com/sense/en-us/pdf/Script%20Syntax%20and%20Chart%20Functions%20Guide.pdf)

I'm not sure, that is it, what you want.

HTH

Re: Can create a function ?

CELAMBARASAN — Fri, 29 May 2015 13:32:29 GMT

Variables can hold a single value, it doesn't store the table.

You have load the table and keep a name for that and use as needed.

To use previously loaded table into memory then

Load *

Resident TableName;

Re: Can create a function ?

Fri, 29 May 2015 13:40:18 GMT

Then, I have to write this table each time I have to use? Because, actually I have to manage a lot of table and if I put this table in each table, the day that I have to correct it will be a little complexe no ?

Re: Can create a function ?

CELAMBARASAN — Fri, 29 May 2015 13:49:00 GMT

What is your expectation, how you are going to use this table?

Re: Can create a function ?

Fri, 29 May 2015 13:57:04 GMT

SET vError = FIRST 1 CONCATENATE(ERROR) LOAD

COUNTRY_CODE,

'$(NbRows)' as NB_ROWS,

'$(ScriptErrorCount)' as ERROR_SEQ_NUMBER,

'$(#ScriptError)' as ERROR_ID,

'$(ScriptError)' as ERROR_NAME,

'$(ScriptErrorDetails)' as ERROR_DETAIL,

'$(ScriptErrorList)' as ERROR_LIST,

now() as ERROR_DATE

resident COUNTRY;

//**********************************************************************//

my_table1:

load

From

Documents;

SET vError;

my_table2:

load

From

Documents;

SET vError;

my_table3:

load

From

Documents;

SET vError;

/****************************************************/

I have this, I want to know if there's something to call "SET vError" as a variable can only have a single value.

Is it mandatory to put "SET vError" table in every tables ?

Re: Can create a function ?

marcus_sommer — Fri, 29 May 2015 13:59:01 GMT

In general such an approach will work with:

SET vError = "CONCATENATE(ERROR)

LOAD FIRST 1

COUNTRY_CODE,

'$(NbRows)' as NB_ROWS,

'$(ScriptErrorCount)' as ERROR_SEQ_NUMBER,

'$(#ScriptError)' as ERROR_ID,

'$(ScriptError)' as ERROR_NAME,

'$(ScriptErrorDetails)' as ERROR_DETAIL,

'$(ScriptErrorList)' as ERROR_LIST,

now() as ERROR_DATE

resident COUNTRY;"

and called simply with:

$(vError)

but the syntax-checker won't recognize them as correct and it is not flexible. Therefore I suggest you to put this into a include-variable and extended it to a generic table like:

include-file:

let vLoadTable = TableName(NoOfTables() - 1);

CONCATENATE(ERROR)

LOAD FIRST 1

COUNTRY_CODE,

'$(NbRows)' as NB_ROWS,

'$(ScriptErrorCount)' as ERROR_SEQ_NUMBER,

'$(#ScriptError)' as ERROR_ID,

'$(ScriptError)' as ERROR_NAME,

'$(ScriptErrorDetails)' as ERROR_DETAIL,

'$(ScriptErrorList)' as ERROR_LIST,

now() as ERROR_DATE

resident $(vLoadTable);

and adds maybe some more useful informations like documentname(), today() ... and so on - see for include: The $(Include) which you $(Must_Include) into your toolkit

- Marcus

Re: Can create a function ?

marcus_sommer — Fri, 29 May 2015 14:03:48 GMT

One addition, maybe it's better to remove the COUNTRY_CODE and put the tablename or something similar within these error-load and use an autogenerate 1;

- Marcus

Re: Can create a function ?

rwunderlich — Fri, 29 May 2015 14:30:03 GMT

I see what you are trying to do and I think it's very clever.

You can use either the $(vError); approach as Marcus suggested or an $(incude).

If using the $(include), be careful that there are no statements in the include before that LOAD, as that would reset the *Error variables..

I think you should also remove the CONCATENATE and just rely on auto concatenation. Then you won't have to pre-create the table.

-Rob

Re: Can create a function ?

Mon, 01 Jun 2015 08:21:23 GMT

Hi Marcus!

Thanks for your help.

I tried your answer and I got a problem. Do you know why my errors are reset? They are all at NULL and 0

REgards

Re: Can create a function ?

Mon, 01 Jun 2015 08:43:56 GMT

Thanks for your help.

I always get the same error. All my Error variable turn NULL when I use $(vError) and when I put my all error script next to my table I get everything I need. If you have a idea I take it!

Regards

Re: Can create a function ?

Mon, 01 Jun 2015 08:49:44 GMT

Thanks for your help,

I try what you sent to me, but the result is NULL after my table. I wonder if this call function doesn't reset my error varible.

Re: Can create a function ?

marcus_sommer — Mon, 01 Jun 2015 09:05:20 GMT

Please post an example (not your original - a small dummy with a few inline-tables).

- Marcus

Re: Can create a function ?

Mon, 01 Jun 2015 09:23:03 GMT

<//include-file:

let vLoadTable = TableName(NoOfTables() - 1);

CONCATENATE(ERROR)

LOAD

//COUNTRY_CODE,

'$(NbRows)' as NB_ROWS,

'$(ScriptErrorCount)' as ERROR_SEQ_NUMBER,

'$(#ScriptError)' as ERROR_ID,

'$(ScriptError)' as ERROR_NAME,

'$(ScriptErrorDetails)' as ERROR_DETAIL,

'$(ScriptErrorList)' as ERROR_LIST,

now() as ERROR_DATE

resident $(vLoadTable);

/**************************************/

	MaxString(AC) AS "GENERIC_1",
	MaxString(AD) AS "GENERIC_2",
	MaxString(AE) AS "GENERIC_3",
	MaxString(AF) AS "GENERIC_4",
	MaxString(AG) AS "GENERIC_5",
	count('*') AS "NB_OF_DUPLICATED"
	FROM
	[ftp://$(AAA):$(AAA)@$(PATH_ADDRESS)/$(vDirectory)/Prospect.xlsx]
	(ooxml, no labels, header is 1 lines, table is DATA)
	where (Len("A") > 0)
	Group By A, B, '$(vCountry)';

	STORE Datas Into (txt); <--- error

	$(include=$(vLoadTable));

/******************************************************************************************/

Result:

Re: Can create a function ?

rwunderlich — Mon, 01 Jun 2015 17:18:42 GMT

Insert the code as Marcus suggested, with

$(vError);

that should work. If it doesn't ,please post the script you are using.

-Rob

Re: Can create a function ?

rwunderlich — Mon, 01 Jun 2015 17:19:40 GMT

The LET statement will reset your error variables.

-Rob

Re: Can create a function ?

rwunderlich — Mon, 01 Jun 2015 17:20:34 GMT

The CALL will reset the Error variable.

-Rob