- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Maybe I made a mistake in my script ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes finally it worked ! Thanks so much for your help.
Regards,
Thanh