How to calculate the frequency of a field in the script?

[francofiorillo]

Hello.

I have the following date table. TAB1

A	B	C	D	E
17/11/2017	17/11/2017	20/11/2017	21/11/2017	22/11/2017
16/11/2017	18/11/2017	20/11/2017	22/11/2017	23/11/2017
20/11/2017	21/11/2017	22/11/2017	23/11/2017	24/11/2017
22/11/2017	23/11/2017	24/12/2017	27/12/2017	28/12/2017
24/11/2017	27/11/2017	28/11/2017	29/11/2017	30/11/2017
20/11/2017	20/11/2017	21/11/2017	22/11/2017	23/11/2017
28/11/2017	29/11/2017	30/11/2017	01/12/2017	04/12/2017
21/11/2017	22/11/2017	23/11/2017	24/11/2017	25/11/2017
22/11/2017	23/11/2017	24/11/2017	27/11/2017	28/11/2017
23/11/2017	24/11/2017	27/11/2017	28/11/2017	29/11/2017

I want to build a TAB2 table in the script, in which, next to each value of a column, I have to calculate the number of times that value appears in the column.

For example, in the following tab, I entered the columns F and G of the frequencies relative to columns A and B of Tab1.

A	F	B	G	C	D	E
17/11/2017	1	17/11/2017	1	20/11/2017	21/11/2017	22/11/2017
16/11/2017	1	18/11/2017	1	20/11/2017	22/11/2017	23/11/2017
20/11/2017	2	21/11/2017	1	22/11/2017	23/11/2017	24/11/2017
22/11/2017	2	23/11/2017	2	24/12/2017	27/12/2017	28/12/2017
24/11/2017	1	27/11/2017	1	28/11/2017	29/11/2017	30/11/2017
20/11/2017	2	20/11/2017	1	21/11/2017	22/11/2017	23/11/2017
28/11/2017	1	29/11/2017	1	30/11/2017	01/12/2017	04/12/2017
21/11/2017	1	22/11/2017	1	23/11/2017	24/11/2017	25/11/2017
22/11/2017	2	23/11/2017	2	24/11/2017	27/11/2017	28/11/2017
23/11/2017	1	24/11/2017	1	27/11/2017	28/11/2017	29/11/2017

can you please help me?

thank you

[rubenmarin]

Hi Francesco, you use mapping tables with the gouped value for each date and use applymap to assign value:

mapA:

Mapping LOAD A

     Count(A)

resident table group by A;

// same for B

FinalTable:

LOAD A,

ApplyMap('mapA', A) as F,

B,

ApplyMap('mapB', B) as G,

..

[petter]

This is an approach that works with LEFT JOIN and GROUP BY:

FOR EACH Fld IN 'A','B','C','D','E'
    LEFT JOIN
    LOAD
      $(Fld),
      Count($(Fld))
    RESIDENT
      Tab1
    GROUP BY $(Fld);
NEXT

[MarcoWedel]

Hi,

another solution might be:

TAB1:
LOAD RecNo() as ID, * FROM [https://community.qlik.com/thread/282187] (html, codepage is 1252, embedded labels, table is @1);

tabTemp:
CrossTable (FieldNam, FieldVal)
LOAD * Resident TAB1;
Join
LOAD FieldNam,
    FieldVal,
    Count(FieldVal) as FieldValFreq
Resident tabTemp
Group By FieldNam, FieldVal;

TAB2:
Generic
LOAD ID,
    FieldNam&'Freq',
    FieldValFreq
Resident tabTemp;

DROP Table tabTemp;

hope this helps

regards

Marco

[MarcoWedel]

or similar to Petter's solution:

SET vTabNam = 'TAB1';

$(vTabNam):
LOAD * FROM [https://community.qlik.com/thread/282187] (html, codepage is 1252, embedded labels, table is @1);

FOR vFldNum = 1 to NoOfFields('$(vTabNam)')
    vFldNam = FieldName(vFldNum,'$(vTabNam)');
    Left Join ($(vTabNam))
    LOAD $(vFldNam),
         Count($(vFldNam)) as $(vFldNam)Freq
    Resident $(vTabNam)
    Group By $(vFldNam);
NEXT

hope this helps

regards

Marco

[francofiorillo]

HI Marco.

perfect !

works great !

many thanks to Marco.

I will also try the other suggestions that seem logically correct.

thank you all.

Hello

[MarcoWedel · Accepted solution]

Hi,

nice to hear that it works for you.

Please close your thread if your question is answered.

Thanks

Regards

Marco