Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

sunny_talwar

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
Author

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

Not applicable
Author

PFA

sunny_talwar

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