Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Pico
Partner - Contributor III
Partner - Contributor III

remove last character (',') from list

Hi All,

I'm having troubles removing the last character from a string (list):

the list is build this way:

 

FieldValues:
LOAD Concat( '[' & [CommonField] & '] ,') as FieldValueList;
LOAD Field as CommonField 
resident FieldCounts
	where Check = 'Y';
    
LET vCommonFields = Peek('FieldValueList',-1,FieldValues);
DROP TABLE FieldValues;

 

Hence is a list of all fields in a tabel, and it has been created in order to make a LOAD statement among many different tables, all with the common fields (these fields are dynamically changing, it's complex), but this is the final load:

 

 DEFINITIVE:
                  Load
                 
                  $(vCommonFields),
                  '$(M)' as INVOICE_MONTH,
                  '$(Y)' as INVOICE_YEAR

                  From [$(sFile)] (parquet);

 

Where M is Month and Y is Year.

Now, the problem is that the list vCommonFields ends with a comma (e.g. [Field1],[Filed2],[Field3],). **bleep** comma.

if I try to remove it with 

 

LET vCommonFields = left('$(vCommonFields)', len(TRIM('$(vCommonFields)')-1);

 

doesn't work. The result is a null, the list is not seen as a string.

How can I solve this?

 

Right now, the only solution is having this:

 

DEFINITIVE:
                  Load
                 
                  $(vCommonFields)
                  '$(M)' as INVOICE_MONTH,
                  '$(Y)' as INVOICE_YEAR

                  From [$(sFile)] (parquet);

 

so removing the comma after the variable, but it's seen as as mistake by Qlik, giving warnings and errors in the automations. So it's not really a solution....

 

Thank you very much

Labels (4)
1 Solution

Accepted Solutions
steeefan
Luminary
Luminary

Instead of ...

LOAD Concat( '[' & [CommonField] & '] ,') as FieldValueList;

try using ...

LOAD Concat('[' & [CommonField] & ']', ',') as FieldValueList;

Refering to the defintion of CONCAT(), Concat(string[, delimiter[, sort_weight]]), you are using ',' inside of your value for string instead of using it as value for delimiter.

View solution in original post

2 Replies
steeefan
Luminary
Luminary

Instead of ...

LOAD Concat( '[' & [CommonField] & '] ,') as FieldValueList;

try using ...

LOAD Concat('[' & [CommonField] & ']', ',') as FieldValueList;

Refering to the defintion of CONCAT(), Concat(string[, delimiter[, sort_weight]]), you are using ',' inside of your value for string instead of using it as value for delimiter.

Pico
Partner - Contributor III
Partner - Contributor III
Author

works perfectly, thank you very much