Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vamshi_1957
Not applicable

Export to text file with fixed length data

Hi

I have written a macro to export the straight table in a qvw to a flat file (.txt) file and was able to specify the delimiter i wish to.

The macro which i used is as follows:

sub Export

Dim docProp

set docProp = ActiveDocument.GetProperties

Dim tmpFile  'used to create relative filepaths

tmpFile = docProp.MyWorkingDirectory

tmpFile = tmpFile & "External Data Sources\OperationalReportsOutput\"

set obj = ActiveDocument.GetSheetObject("CH01")

obj.Export tmpFile & "Activity" & ".txt",";"

end sub

Now i have to change the output data to a fixed length datatype. It means for suppose, i have 3 fields

Name  Address              Phonenumber

Sam    Greenville,USA    2159627856

Vizard Charlotte, USA    2527255632

If i gave a fixed length of 20 to the fields:and i specify no delimiter,

the output should look like

Sam                 Greenville,USA      2139627856

Vizard               Charlotte,USA       2527255632

So every record in the text file should end at the same point.

Any ideas!

Tags (1)
1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: Export to text file with fixed length data

Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)


talk is cheap, supply exceeds demand
12 Replies
Gysbert_Wassenaar
Not applicable

Re: Export to text file with fixed length data

You could do this by creating a straight table with only one expression that concatenates the fields into one string :

= left(Name,20) & repeat(' ', 20-len(Name1)) &  left(Address,20) & repeat(' ', 20-len(Address)) &  left(Phonenumber,20) & repeat(' ', 20-len(Phonenumber)).

Then export that straight table.


talk is cheap, supply exceeds demand
vamshi_1957
Not applicable

Re: Export to text file with fixed length data

Hi Gysbert Wassenaar,

That was a helpful solution, but they just want to know is it possible to do it in the macro so that the output file shows the data in the way they wish, without any change to the straight table

How can i specify it in the macro! i have no idea!

Not applicable

Re: Export to text file with fixed length data

In Qlikview, we don't have data types for fixed length fields. I suggest Please follow

LOAD IF(Len(Name) > 20, LEFT(Name,20) , Name & Repeat(' ', 20-Len(Name)) ) AS Name,

          IF(Len(Address) > 20, LEFT(Address,20) , Address& Repeat(' ', 20-Len(Address)) ) AS Address,

          IF(Len(Phonenumber) > 20, LEFT(Phonenumber,20) , Name & Repeat(' ', 20-Len(Phonenumber)) ) AS Phonenumber

SOURCE;

Gysbert_Wassenaar
Not applicable

Re: Export to text file with fixed length data

You don't have to change the original straight table. Create another straight table and hide it. Just make the button (or whatever) that calls the macro export the hidden straight table instead of exporting the original one.


talk is cheap, supply exceeds demand
vamshi_1957
Not applicable

Re: Export to text file with fixed length data

Hi Dathu

Thank you for your suggestion. I don't need the fixed length in qlikview. What i am asking is can we define the fixed length field while exporting to a text file. I mean it in the macro script. Just want to know can we do it in vbscript! If no other option is availabe, i can go with Gysbert Wassenaar's method.

vamshi_1957
Not applicable

Re: Export to text file with fixed length data

Gysbert Wassenaar

I have tried the method you said, by creating a new straight table with just one expression. But it is showing the output only when one Name or only one phonenumber is selected. without any selections, it is showing nothing!

Do i need to change something!

Gysbert_Wassenaar
Not applicable

Re: Export to text file with fixed length data

Ah, my mistake. Here's what you can do: Create a calculated dimension using the expression I posted. Then add an expression to the straight table, for example just 1 as expression will do. Set the total mode to None and then hide the expression on the presentation tab. The straight table with just that calculated dimension and the hidden expression should result in a table that will export to the format you want.


talk is cheap, supply exceeds demand
vamshi_1957
Not applicable

Re: Export to text file with fixed length data

I am using a aggregation function in the expression.

=  left(TlrNbr,20) & repeat(' ', 20-len(TlrNbr)) &  left(TlrName,20) & repeat(' ', 20-len(TlrName)) & left(Count(BrAccount),20) & repeat(' ', 20-len(Count(BrAccount)))

So, i think we cannot use it as a calculated dimension.

Gysbert_Wassenaar
Not applicable

Re: Export to text file with fixed length data

Try replacing count(BrAccount) with aggr(Count(BrAccount),TlrNbr,TlrName)


talk is cheap, supply exceeds demand