topic Create a counting table by ID in Connectivity & Data Prep

Create a counting table by ID

pedrohenriqueperna — Tue, 16 Jan 2024 01:43:38 GMT

Hi,

I'm having trouble to build a counting table of a "flag" table.

This flag table holds about 15 values that might be common between the different fields, eg:

FlagTable:

LOAD * Inline [email, phone, name, address, birthday

error, ok, ok, empty, error

ok, ok, error, empty, error

ok, ok, ok, empty, ok

error, ok, empty, ok, ok

];

I've built this table and now I need to put the values as columns and the column names as a dimension:

CountTable:

LOAD * Inline [field_name, ok, error, empty

phone

name

address

birthday

];

So I can end up with a table holding the count for each new column (ok, error, empty):

field_name | ok | error | empty

email | 2 | 2 | 0

phone | 4 | 0 | 0

name | 2 | 1 | 1

address | 1 | 0 | 3

birthday | 2 | 2 | 0

What I tried to do is to conditionally count the values and group them by field_name. For that I've built a loop based on CountTable rows and peeked each field_name, to concatenate the rows and built the complete table (not sure if it's possible without a loop).

LET vFieldName = Peek('field_name', $(i), 'CountTable')

Temp_Count:

NoConcatenate LOAD

'$(vFieldName)' as field_name

,Count(If($(field_name) = 'ok', $(vFieldName))) as ok

,Count(If($(field_name) = 'error', $(vFieldName))) as error

,Count(If($(field_name) = 'empty', $(vFieldName))) as empty

Resident FlagTable
Group By $(vFieldName);

I tried to make it simpler, didn't add the loop, etc, but I think you can see where I'm trying to get. The problem with this final table is that it doesn't group by the dimension, but gives me 1 row for each value instead (ok, error, empty in this case). So after concatenating the different field_name, I have 15 rows instead of 5.

Could anyone help me out?

Re: Create a counting table by ID

MayilVahanan — Tue, 16 Jan 2024 03:19:49 GMT

Try like below

Test:

CrossTable(Key, State)

LOAD RowNo() as SNO, * Inline [email, phone, name, address, birthday

error, ok, ok, empty, error

ok, ok, error, empty, error

ok, ok, ok, empty, ok

error, ok, empty, ok, ok

];

Left Join (Test)

LOAD Key,

State,

Count(State) as Count

Resident Test

Group By Key, State;

FinalTable:

LOAD DISTINCT Key

Resident Test;

FOR i = 1 to FieldValueCount('State')

LET vState = FieldValue('State', $(i));

Left Join (FinalTable)

LOAD DISTINCT Key,

Count as [$(vState)]

Resident Test

Where State = '$(vState)';

DROP Table Test;

Re: Create a counting table by ID

pedrohenriqueperna — Tue, 16 Jan 2024 13:19:12 GMT

Hi, @MayilVahanan

Thank you very much!! It worked perfectly adapting as the following:

Test:
CrossTable(Key, State)
LOAD RowNo() as SNO,
espelho_email,
espelho_nome,
espelho_rua,
espelho_bairro,
espelho_cidade,
espelho_complemento,
espelho_telefone,
espelho_celular,
espelho_nascimento
From [$(vTDS)/Cliente_Espelho.qvd] (qvd);

Left Join (Test)
LOAD Key,
State,
Count(State) as Count
Resident Test
Group By Key, State;

FinalTable:
LOAD DISTINCT Key
Resident Test;

FOR i = 1 to FieldValueCount('State')

LET vState = FieldValue('State', $(i));

Left Join (FinalTable)
LOAD DISTINCT Key,
Count as [$(vState)]
Resident Test
Where State = '$(vState)';

NEXT

DROP Table Test;

I'm now curious to understand the approach. I understand Crosstable will do the job of transposing the field values to columns, but what is RowNo doing and "SNO" as a new field? (specially SNO, because it seems to not be used further in the script).

I also got a bit confused on what happened in the first count table you joined to "Test" table.

Could you explain me better, just for the sake of knowledge?

Re: Create a counting table by ID

pedrohenriqueperna — Tue, 16 Jan 2024 15:35:36 GMT

One more doubt:

If I had to consider a date field that exists within the flag table before we transposed the fields, but in this case I wouldn't transpose the date field, but use it as a dimension to group the counting. How would it be done?

Instead of a single row for each Key, I'd have 5 Keys for each date present in the table, e.g:

date_field | field_name | ok | error | empty

01/01/24 | email | 2 | 2 | 0

01/01/24 | phone | 4 | 0 | 0

01/01/24 | name | 2 | 1 | 1

01/01/24 | address | 1 | 0 | 3

01/01/24 | birthday | 2 | 2 | 0

02/01/24 | email | 12 | 8 | 3

02/01/24 | phone | 74 | 8 | 5

02/01/24 | name | 22 | 11 | 19

02/01/24 | address | 1 | 10 | 32

02/01/24 | birthday | 24 | 29 | 30

etc...

Re: Create a counting table by ID

pedrohenriqueperna — Tue, 16 Jan 2024 19:22:57 GMT

Regarding adding an extra date field as a dimension, I removed the RowNo as qualification field and added date_field. Then loaded DISTINCT 'Key' but also 'date_field' and it worked.

I suppose RowNo had the purpose of being a qualification field, as it will generate "ids" for each line. Considering date_field is also an "id" it worked when substituting.

Thank you very much again, I probably wouldn't have done it by myself or it'd have taken me too long.

Re: Create a counting table by ID

MayilVahanan — Wed, 17 Jan 2024 07:49:10 GMT

Yes, you are right. if you have Date field , you can use as Id column. If there is no field, but need to transpose all the field, then Rowno() act as the "ID" field.