Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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:Smiley Tongueending|:

defect::1236|:defectfrom:Q9999|:defectto::Q1111|:component:Smiley Tongueayments|:status:Smiley Surprisedpen|:


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
MVP & Luminary
MVP & Luminary

Re: Calling data from textfile into qliksense/qlikview

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
Valued Contributor II

Re: Calling data from textfile into qliksense/qlikview

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

Re: Calling data from textfile into qliksense/qlikview

‌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.

MVP & Luminary
MVP & Luminary

Re: Calling data from textfile into qliksense/qlikview

Please provide an example of your text-file.

- Marcus

Not applicable

Re: Calling data from textfile into qliksense/qlikview

Here you go marcus.


This is the sample data from the text file.


defect::1234|:workorigin::Q9999|:workowning::Q1111|:component:Smiley Tongueayments|:status::closed|:

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

defect::6747|:workorigin::Q9777|:workowning::Q9999|:component:Smiley Tongueayments|:status::closed|:

defect::2485|:workorigin::Q9777|:workowning::Q1111|:component:Smiley Surprisedthers|:status::closed|:

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

defect::4350|:workorigin::Q9777|:workowning::Q9999|:component:Smiley Tongueayments|:status::withdrawn|:

MVP & Luminary
MVP & Luminary

Re: Calling data from textfile into qliksense/qlikview

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:Smiley Tongueayments|:status::closed|:

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

defect::6747|:workorigin::Q9777|:workowning::Q9999|:component:Smiley Tongueayments|:status::closed|:

defect::2485|:workorigin::Q9777|:workowning::Q1111|:component:Smiley Surprisedthers|:status::closed|:

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

defect::4350|:workorigin::Q9777|:workowning::Q9999|:component:Smiley Tongueayments|:status::withdrawn|:

];

- Marcus

Not applicable

Re: Calling data from textfile into qliksense/qlikview

Hi Marcus,

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

can u please post me the qvw file.

MVP & Luminary
MVP & Luminary

Re: Calling data from textfile into qliksense/qlikview

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

Re: Calling data from textfile into qliksense/qlikview

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'