Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
thanhng34
Partner
Partner

Forced concatenation of multiple files

Hello community,

I need your help to add some calculated fields to my files, before concatenating all of my 8 files.

thanhng34_0-1606311064828.png


I would like to :

- Add an "IP" field in all the files:
IP = 'CAPI "if the name of the file starting with' capi ';
IP = 'CPCEA' if the name of the file starting with 'cpcea'.


- Add an "Activity" field in all files:
Activity = 'adhesion' if the file name contains 'adh'
Activity = 'affiliation' if the file name contains 'affil'
Activity = 'accompte' if the name of the file contains 'acpte'
Activity = 'reglement' if the name of the file contains 'rglt'

- Concatenate all these 8 files.

How can I do it in script?

Thanks so much in advance for your help.

Labels (4)
2 Solutions

Accepted Solutions
brunobertels
Specialist III
Specialist III

Hello 

Add this in your script : 

load

subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activity,

...

from etc 

 

Et çà devrait marcher 😉

View solution in original post

PrashantSangle

use force concatenate

Test:

Load 'dummy' as id;

For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')

concatenate

LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité

From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile;

 

then you can remove the 1st dummy line from your table;

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

7 Replies
brunobertels
Specialist III
Specialist III

Hello 

Add this in your script : 

load

subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activity,

...

from etc 

 

Et çà devrait marcher 😉

thanhng34
Partner
Partner
Author

Hi,

Thanks for your solution. I tried adding this to my script but it did'nt work (so many "$Syn"), while the concatenation of one by one file (by "CONCATENATE" function) worked .

Here is my script :

For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')

LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité

From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile

thanhng34_0-1606381808226.png

Maybe I made a mistake in my script ? 

thanhng34
Partner
Partner
Author

I think It didn't work because my files do not have exactly the same set of fields (they have some common fields, but also different ones), so I have to make a forced concatenation. 

But I don't know how to correct my script as I have a lot of files, and I don't want to repeat the CONCATENATE functions...

PrashantSangle

for adding field IP use filename() 

 

like Load *, if(wildmatch(filename(),'capi*','CAPI','CPCEA') as IP

 

for activity field() also use combination of wildmatch() and filename()

 

like 

if(wildmatch(filename(),'*adh*'),'adhesion'),if(wildmatch(filename(),'*affil*'),'affiliation'))) as activity

 

regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
thanhng34
Partner
Partner
Author

Hi @PrashantSangle and @brunobertels ,

Thanks for your solution. I managed to create the fields 'IP' and 'Activity" with your suggestions, but the problem is that I don't know how to concatenate my 8 files which do not have the same set of fields. I want to make a forced concatenation.

Here is my script :

For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')

LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité

From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile

 

PrashantSangle

use force concatenate

Test:

Load 'dummy' as id;

For each vFile in FileList('lib://$(vConnexionFichiersDAP)/DSN/Suivi Anomali/Suivi/*.csv')

concatenate

LOAD
*,
subfield(Filename(),'_',1) as IP,
replace(replace(replace(replace(subfield(Filename(),'_',2),'acpte','Accompte'),
'adh','Adhésion'),
'affil','Affiliation'),
'rglt','Réglement')
as Activité

From [$(vFile)] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);
Next vFile;

 

then you can remove the 1st dummy line from your table;

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
thanhng34
Partner
Partner
Author

Yes finally it worked ! Thanks so much for your help.

Regards,

Thanh