Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Generic load or cross table in Qliksense

Hello folks,

I have unstructured data in text file (attached), the column names are available in row. I am trying to get data like below.. Is it possible in qliksense? then how? Can any one tell me how to process. I have loaded data from text file, however cannot able to transform them.

1-grams     2-grams     3-grams     4-grams     5-grams

0                    0               0                    0               0

1                    1               1                    1               1

2                    1               1                    1                 1

..

Can we use cross table to get it?

5 Replies
balrajahlawat
Esteemed Contributor

Re: Generic load or cross table in Qliksense

MVP
MVP

Re: Generic load or cross table in Qliksense

Try this script:

Table:

LOAD [1-grams],

     [2-grams],

     [3-grams],

     [4-grams],

     [5-grams]

FROM

[Unstructured Data.txt]

(txt, codepage is 1252, embedded labels, delimiter is ' ', msq, filters(

Transpose()

));

Not applicable

Re: Generic load or cross table in Qliksense

I have tried this Sunny. But each column is not getting all values Pls see attached file for mail tread.

Not applicable

Re: Generic load or cross table in Qliksense

PFA

MVP
MVP

Re: Generic load or cross table in Qliksense

Check this code out:

Table:

LOAD Right(@1, Len(@1)-Index(@1, ' ')) as @1,

  SubField(@1, ' ', 1) as @2

FROM

[lib://File/Unstructured Data.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

STORE Table into [lib://File/Table.qvd] (qvd);

DROP Table Table;

Table:

LOAD If(@2 = Peek('@2'), RangeSum(Peek('Flag'), 1), 1) as Flag,

  @2,

  SubField(@1, ' ') as @1    

FROM

[lib://File/Table.qvd]

(qvd);

STORE Table into [lib://File/Table.qvd] (qvd);

DROP Table Table;

Table:

Generic

LOAD Flag,

  @2,

     @1

FROM

[lib://File/Table.qvd]

(qvd);

SET vListOfTables = ;

FOR vTableNo = 0 to NoOfTables()

  LET vTableName = TableName($(vTableNo));

   

    IF Subfield(vTableName,'.',1) = 'Table' Then

  

  Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39);

  ENDIF

NEXT vTableNo

CombinedGenericTable:

LOAD Flag

FROM

[lib://File/Table.qvd]

(qvd);

FOR Each vTableName in $(vListOfTables)

  Left Join (CombinedGenericTable)

  Load *

  Resident [$(vTableName)];

   

    Drop Table [$(vTableName)];

NEXT vTableName