Help : Create a text file in semi colon seperated format from yxdb file

[alt_tush]

Hi Team,

I need your urgent help here.

I have a yxdb file with blow type of data

Filed1  Filed2             Filed3             Filed4               Filed5       Filed6       Filed7   Filed8
X         09141             11/30/2017    12/7/2017       
Y         3B670931       24                  XYZ CO LTD    UK            478886   
Y         3B670931       24                  XYZ CO LTD    UK            478886   
Y         3B670931       24                  XYZ CO LTD    UK            478886   
Z         12345              25                 ABX,PVT LTD   US            123456    200        AUD
Z         12345              25                 ABX,PVT LTD   US            123456    200        AUD

I want to convert it into below format in text file (.txt) file

Output :-

X;09141;11/30/2017;12/7/2017;
Y;3B670931;24;XYZ CO LTD;UK;478886;
Y;3B670931;24;XYZ CO LTD;UK;478886;
Y;3B670931;24;XYZ CO LTD;UK;478886;
Z;12345;25;ABX,PVT LTD;US;123456;200;AUD;
Z;12345;25;ABX,PVT LTD;US;123456;200;AUD;
C;6;

In bold there is a comma in between the string. I need the value as shown as per the input.

Last row is the count of records i.e. 6 is the number of records in above data and character "C" is fixed. i.e. last row would be C;6

I need your urgent help here.

Waiting for your reply.

Thank you.

[LordNeilLord]

Hey @alt_tush

Here are the steps to achieve your output:

1. Add a recordid tool
2. Transpose the data grouping by recordid only
3. Summarize tool grouping by recordid and concatenating value (using semicolon seperator)
4. Add another summarize (after recordid again) getting max recordid
5. Add a formula tool updating max_recordid with the prefix "c"
6. Union the 2 steams together (using position)



@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[alt_tush]

Hi Neil,

Thank you so much for your quick response.

I have checked your workflow. It shows me error in summarize control where error like "The field "RecordId" is not contained in the record

(Action=Max)

Error: Summarize (6): RecordInfo::CreateRecord: A record was created with no fields.

This error shows me in both the summarize control.

Could you please help me here.

Thanks once again :)

[LordNeilLord]

Hey @alt_tush

Are you using the workflow I sent you? I don't get the same error.

Can you send the workflow you are using and I'll see what the error is?

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[alt_tush]

Hi Neil,

I cant share the workflow. As it is restricted from my side to upload any data outside the organization.

But I am checking your workflow only but it seems in summarized tool there is only two fields Name and Value.

And you are selected "RecordId" in Actions Properites which is not part of the fields. Hence it shows the message The field "RecordId" is not contained in the record.

Thank you very much for your response.

[alt_tush]

Hey Neil,

It works now :)

but in text file I got the output in below format

"X;09141;11/30/2017;12/7/2017"
"Y;3B670931;24;XYZ;CO;LTD;UK;478886"
"Y;3B670931;24;XYZ;CO;LTD;UK;478886"
"Y;3B670931;24;XYZ;CO;LTD;UK;478886"
"Z;12345;25;ABX,PVT;LTD;US;123456;200;AUD"
"Z;12345;25;ABX,PVT;LTD;US;123456;200;AUD"
"C;6"

I don't want " " (Doublt quotes) on starting on the line and ending of the line.

an you please tell me which property should I select to avoid this double quotes while saving the file in .txt format?

I need output like this :-

X;09141;11/30/2017;12/7/2017
Y;3B670931;24;XYZ;CO;LTD;UK;478886
Y;3B670931;24;XYZ;CO;LTD;UK;478886
Y;3B670931;24;XYZ;CO;LTD;UK;478886
Z;12345;25;ABX,PVT;LTD;US;123456;200;AUD
Z;12345;25;ABX,PVT;LTD;US;123456;200;AUD
C;6

Once again thanks a lot :)

[LordNeilLord]

Hey @alt_tush

I think you are missing the "Group By" on the Transpose tool....it should be configured like this:

If thats still not fixed it....send me your workflow with all of the inputs and outputs removed (so I cant see the data)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[LordNeilLord]

Hey @alt_tush

No problem, glad you've got it working.

To remove the quotes you need to set the "Quote output Fields" option. I also noticed while testing that it throws an error when trying to output unless you set the delimiters to \0..not sure if you had this issue?

If this is all working now, I'd be super grateful if you could accept the solutions for me :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[alt_tush]

Hi Neil,

Yes I works now.

But in text file I got the below output which is correct. But I have problem with two lines (check in bold color)

X;09141;11/30/2017;12/7/2017
Y;3B670931;24;XYZ;CO;LTD;UK;478886
Y;3B670931;24;XYZ;CO;LTD;UK;478886
Y;3B670931;24;XYZ;CO;LTD;UK;478886
"Z;12345;25;ABX,PVT;LTD;US;123456;200;AUD"
"Z;12345;25;ABX,PVT;LTD;US;123456;200;AUD"
C;6

where two lines in bold show me the double quotes at the start and end of the line in output file.

I think this comes because of "ABX,PVT" value. Where alteryx cant read it and whole line add into the double quotes.

How to handle such situation where already comma into the string?

Thank you

[LordNeilLord]

Hey @alt_tush,

See my previous post about the delimiters :)

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome