Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using the file target endpoint to output CSV files from database table. These CSV files are use as input to another software system. The interface specification demand that the columns are followed according to document.
For example: Table "User", has 3 columns "Name", "Age", "Gender"
The CSV file must be generated with columns in this sequence. i.e.
"Name","Age","Gender"
"John","13","Male"
"Dave","14","Male"
"Jane","12","Female"
In Qlik Replicate , I am able to remove columns that are not required. But, I do not see how I can explicitly manage the column order. So, sometime after some schema update in the source database table, I might get:
"Name","Gender","Age"
"John","Male","13"
"Dave","Male","14"
"Jane","Female","12"
which is not what is agreed upon. Any advise on how I can control the column sequence order?
Hello,
Unfortunately there is no way to control the order of columns for a File Target, unlike with let's say an Oracle or SQL Server target where in you can create the table manually in the target with the desired column order then set Replicate as Truncate before Loading.
I suggest submitting a feature request thru Ideation if this is a required use case.
Thanks
Lyka
Hello,
Unfortunately there is no way to control the order of columns for a File Target, unlike with let's say an Oracle or SQL Server target where in you can create the table manually in the target with the desired column order then set Replicate as Truncate before Loading.
I suggest submitting a feature request thru Ideation if this is a required use case.
Thanks
Lyka
Hey Lyka,
I am also not able to find the headers in the target CSV file, which is super confusing, is there a way to add table header on the list? Thank you.
--Mengyun
Hi Mengyun,
What table header are you looking for? If metadata header, you can enable that on the target endpoint definition
Thanks
Lyka
As mentioned by Lyka, you can do so at the Manage Endpoints Connections under the Add metadata header section.
Hey Bengseng,
Thanks for your reply, the problem is that I could not find this option for me , either on the Manage Endpoints Connections or the task settings, my source point is from DB2 z/OS, even in Advanced tab:
Hello @bengseng @lyka @qlik-lmy-se ,
I found an easy way to control the columns sequence for generic File Target Endpoint. The basic idea is using table's transformation to adjust the order of the desired columns.
I'm using Oracle source (the solution is irrelevant to the Source Endpoing Type) and File Target to demo how to control the columns order.
In my demo the source table looks like:
create table testcols (id integer, name char(20), address char(20), address2 char(20));
In the demo we are assuming the desired order of the columns in File Target become:
ID, address, address2, name
Please take note some columns order maybe cannot be adjusted if they are part of the PK (In my sample I'm assuming the column "ID" cannot be adjusted) because they cannot be removed from the transformation. So the PK columns will appear prior to other general columns.
The steps are:
1. Remove all the columns from Transform (keep column "ID" as is because it's PK)
2. Add a new column (you choose a name for it) and the expression is simple: you add the original column name as the expression (certainly you can compose your owner expression if needed)
You see I added three new columns. For example one of the new columns name is "2" and its expression is "$ADDRESS".
3. You can see the results in both FL file and __ct table as below:
BTW, for your question where you can add the column header:
Hope this helps.
Regards,
John WANG.