Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calling data from textfile into qliksense/qlikview

Hi,

I have a .txt file which has data as below:

defect::1234|:defectfrom:Q1111|:defectto::Q9999|:component::billing|:status::completed|:

defect::1235|:defectfrom:Q1111|:defectto::Q1111|:component::billing|:status::pending|:

defect::1236|:defectfrom:Q9999|:defectto::Q1111|:component::payments|:status::open|:


this .txt file has no headers.


My requirement is to pull that data into qliksense and convert it into a .QVD file.


The challenge here is getting the column names/Headers pulled.


can someone guide me on how we can pull such a kind of data


Thanks in Advance.

John

12 Replies
marcus_sommer

You could split these records with subfield(), maybe in this way:

table:

Load

subfield(subfield(YourField, '|', 1), '::', 2) as Defect,

subfield(subfield(YourField, '|', 2), '::', 2) as DefectFrom,

subfield(subfield(YourField, '|', 3), '::', 2) as DefectTo,

subfield(subfield(YourField, '|', 4), '::', 2) as Component,

subfield(subfield(YourField, '|', 5), '::', 2) as Status

From TXT;

store table into table.qvd (qvd);

- Marcus

lakshmikandh
Specialist II
Specialist II

Try something like this

TxtF:

LOAD A,

     B,

     etc,...

FROM

(txt, codepage is 1252, delimiter is '|', msq);

STORE TxtF into C:\File.qvd;

Not applicable
Author

‌Hi Marcus,

Thanks for the quick response.

THis holds good few lines of data.

what if my text file has multiple lines of Similar kind of data.

marcus_sommer

Please provide an example of your text-file.

- Marcus

Not applicable
Author

Here you go marcus.


This is the sample data from the text file.


defect::1234|:workorigin::Q9999|:workowning::Q1111|:component::payments|:status::closed|:

defect::3534|:workorigin::Q9999|:workowning::Q1111|:component::billing|:status::closed|:

defect::6747|:workorigin::Q9777|:workowning::Q9999|:component::payments|:status::closed|:

defect::2485|:workorigin::Q9777|:workowning::Q1111|:component::others|:status::closed|:

defect::8504|:workorigin::Q9999|:workowning::Q1111|:component::billing|:status::closed|:

defect::4350|:workorigin::Q9777|:workowning::Q9999|:component::payments|:status::withdrawn|:

marcus_sommer

It worked like expected:

table:

Load

subfield(subfield(YourField, '|', 1), '::', 2) as Defect,

subfield(subfield(YourField, '|', 2), '::', 2) as DefectFrom,

subfield(subfield(YourField, '|', 3), '::', 2) as DefectTo,

subfield(subfield(YourField, '|', 4), '::', 2) as Component,

subfield(subfield(YourField, '|', 5), '::', 2) as Status;

load * inline [

YourField

defect::1234|:workorigin::Q9999|:workowning::Q1111|:component::payments|:status::closed|:

defect::3534|:workorigin::Q9999|:workowning::Q1111|:component::billing|:status::closed|:

defect::6747|:workorigin::Q9777|:workowning::Q9999|:component::payments|:status::closed|:

defect::2485|:workorigin::Q9777|:workowning::Q1111|:component::others|:status::closed|:

defect::8504|:workorigin::Q9999|:workowning::Q1111|:component::billing|:status::closed|:

defect::4350|:workorigin::Q9777|:workowning::Q9999|:component::payments|:status::withdrawn|:

];

- Marcus

Not applicable
Author

Hi Marcus,

this is my text file and i'm unable to load it in qliksense.

can u please post me the qvw file.

marcus_sommer

Try this:

table:

Load

subfield(subfield(YourField, '|', 1), '::', 2) as Defect,

subfield(subfield(YourField, '|', 2), '::', 2) as DefectFrom,

subfield(subfield(YourField, '|', 3), '::', 2) as DefectTo,

subfield(subfield(YourField, '|', 4), '::', 2) as Component,

subfield(subfield(YourField, '|', 5), '::', 2) as Status;

LOAD [@1:n] as YourField

FROM

[YourPath\data.txt]

(fix, codepage is 1252);

- Marcus

Not applicable
Author

Hi Marcus, thank u i have modified it bit without the yourfield

when loading the data i have given labels as none as my text file doesn't have  'yourfiled'