File Target Endpoint : explicit control of column sequence

[bengseng]

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?

[lyka · Accepted solution]

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

[qlik-lmy-se]

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

[lyka]

Hi Mengyun,

What table header are you looking for?  If metadata header, you can enable that on the target endpoint definition

Thanks

Lyka

[bengseng]

As mentioned by Lyka, you can do so at the Manage Endpoints Connections under the Add metadata header section.

[qlik-lmy-se]

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:

[john_wang]

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.

[lyka]

Hello,

I tested this with version 7 and tried to change the order of columns but it didnt work.

I'll test this again tomorrow.

thanks
lyka