Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
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