topic Re: Recurrence of all the words in a field in QlikView

Recurrence of all the words in a field

Mon, 04 Aug 2014 10:15:04 GMT

Hello Everyone,

I need to create a table with the recurrence of all the words in that field.

field1

two dog dog in the car two car

field2

dog on the box

field3

....

Table_recurrences:

FieldID , Word, Recurrence

field1, dog, 2,

field1, two, 2,

field1, car, 2,

field1, in, 1,

field1, the, 1,

field2, dog, 1,

field2, box, 1,

field2, on, 1,

field2, the, 1,

field3.....

...

Thanks in advance

Mirco

Re: Recurrence of all the words in a field

sujeetsingh — Mon, 04 Aug 2014 10:20:40 GMT

Scripts handles these kind of re-occurrence .

You can use String functions and previous function to do this with a loop running and checking the limit.

But in your case the strings are not fixed have any idea how are you going to limit it .

What about your real time values can they have more variations.

Re: Recurrence of all the words in a field

maxgro — Mon, 04 Aug 2014 10:41:11 GMT

PFA

Re: Recurrence of all the words in a field

flipside — Mon, 04 Aug 2014 10:50:41 GMT

Hi,

Use a combination of crosstable (to get all strings into one field) and substring/iterno() to pick out the individual words.

Load rowno() as rowid, * inline [
field1, field2
two dog dog in the car two car, dog on the box
];

data2:
crosstable (field_source,field_data) Load * resident data;

data3:
Load
rowid,
field_source,
dual(subfield(field_data,' ',iterno()),rowno()) as field_word
resident data2
while iterno()<=SubStringCount(field_data,' ')+1;

drop table data2;

flipside