Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Field to Record

Dear All,

I have .txt files. A lot.

Each has a lot of record.

Each record has 3+28 field I needed.

I would like that the 28 field became 28 record.

How?

Thank you for your kind reply!

BR.

Jozsef

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Hi

I think you need to use the crosstable function.

Test:

CrossTable(Header, Data, 2)

LOAD

    @5 as Date,

    @6 as Field1,

    @28,

    @29,

    @30,

    @31,

    @32,

    @33,

    @34,

    @35,

    @36,

    @37,

    @38,

    @39,

    @40,

    @41,

    @42,

    @43,

    @44,

    @45,

    @46,

    @47,

    @48,

    @49,

    @50,

    @51,

    @52,

    @53,

    @54,

    @55,

    @56

FROM

[..Downloads\source.txt] (replace with your file location)

(txt, utf8, no labels, delimiter is ';', msq);

Drop field Header;

View solution in original post

5 Replies
Highlighted
Creator III
Creator III

Hi

I think you need to use the crosstable function.

Test:

CrossTable(Header, Data, 2)

LOAD

    @5 as Date,

    @6 as Field1,

    @28,

    @29,

    @30,

    @31,

    @32,

    @33,

    @34,

    @35,

    @36,

    @37,

    @38,

    @39,

    @40,

    @41,

    @42,

    @43,

    @44,

    @45,

    @46,

    @47,

    @48,

    @49,

    @50,

    @51,

    @52,

    @53,

    @54,

    @55,

    @56

FROM

[..Downloads\source.txt] (replace with your file location)

(txt, utf8, no labels, delimiter is ';', msq);

Drop field Header;

View solution in original post

Highlighted
Not applicable

Hi

I agree. The crosstable function will be the most effective to the results you need.

Adopt the method used by Daniel and you should get th results with a bit of trial and error.

Highlighted
Contributor III
Contributor III

Dear Daniel,

Just what I needed! Thank you very much!

BR.

Jozsi

Highlighted
Creator III
Creator III

Glad to be of assistance.

Please mark as correct if it answers you question.

This helps other community answers.

Thanks

Dan

Highlighted
Contributor III
Contributor III

Dear !Dan,

Could you please help me a little bit?

What if I need a bit different result, something like This:

10/24/2016 14:19:47;   0303F8400660; CF5595001; A; 1;

10/24/2016 14:19:47;   0303F8400660; CF4895001; A; 2;

10/24/2016 14:19:47;   0303F8400660; CF4195001; A; 3;

10/24/2016 14:19:47;   0303F8400660; CF3495001; A; 4;

10/24/2016 14:19:47;   0303F8400660; CF5495001; A; 5;

10/24/2016 14:19:47;   0303F8400660; CF4795001; A; 6;

10/24/2016 14:19:47;   0303F8400660; CF4095001; A; 7;

10/24/2016 14:19:47;   0303F8400660; CF3395001; B; 1;

10/24/2016 14:19:47;   0303F8400660; CF5395001; B; 2;

10/24/2016 14:19:47;   0303F8400660; CF4695001; B; 3;

10/24/2016 14:19:47;   0303F8400660; CF3995001; B; 4;

10/24/2016 14:19:47;   0303F8400660; CF3295001; B; 5;

10/24/2016 14:19:47;   0303F8400660; CF5295001; B; 6;

10/24/2016 14:19:47;   0303F8400660; CF4595001; B; 7;

10/24/2016 14:19:47;   0303F8400660; CF3895001; C; 1;

10/24/2016 14:19:47;   0303F8400660; CF3195001; C; 2;

10/24/2016 14:19:47;   0303F8400660; CF5195001; C; 3;

10/24/2016 14:19:47;   0303F8400660; CF4495001; C; 4;

10/24/2016 14:19:47;   0303F8400660; CF3795001; C; 5;

10/24/2016 14:19:47;   0303F8400660; CF3095001; C; 6;

10/24/2016 14:19:47;   0303F8400660; CF5095001; C; 7;

10/24/2016 14:19:47;   0303F8400660; CF4395001; D; 1;

10/24/2016 14:19:47;   0303F8400660; CF3695001; D; 2;

10/24/2016 14:19:47;   0303F8400660; CF2995001; D; 3;

10/24/2016 14:19:47;   0303F8400660; CF4995001; D; 4;

10/24/2016 14:19:47;   0303F8400660; CF4295001; D; 5;

10/24/2016 14:19:47;   0303F8400660; CF3595001; D; 6;

10/24/2016 14:19:47;   0303F8400660; CF2895001; D; 7;

But the source is the same as was last time.

The new part is the last two field. It isn't on the source file.

There is any possibilities I sort it out during the load phase or I have to prepare the data structures before I load it?

I hope it is clear.

Thank you for your help!