Add a specific value for each key element in the table

[francesctesi]

Hi,

I would like to add a default value, to be able to do measurements later, for each key in the table.
Specifically, I have a table with the following fields.

For each key, I would like to add a default value in the 'CODE' column, with a default value (ex:9999), with a fixed category value (B), and a fixed value for the last column (ex:0).

Obtaining a similar result,with this new rows:

thanks!

[Chanty4u]

Hi try something. Like this 

LOAD * FROM [your_data_source];

DefaultValues:

LOAD

    Key,

    '9999' AS CODE,

    'B' AS Category,

    0 AS LastColumn

AUTOGENERATE Key;

TableWithDefaultValues:

LOAD

    Key,

    ApplyMap('DefaultValues', Key, '9999') AS CODE,

    ApplyMap('DefaultValues', Key, 'B') AS Category,

    ApplyMap('DefaultValues', Key, 0) AS LastColumn

RESIDENT ExistingTable;

[francesctesi]

HI, THANK!

 but the script gives me this error when i try to create  the table "DefaultValues" : 

Autogenerate: generate count is out of range

And I don't understand how it can link the values inside the 'key' field, if I first auto-generate them randomly?

[Kushal_Chawda · Accepted solution]

@francesctesi  try below

Data:
Load KEY,
     CODE,
     CATEGORY,
     VALUE
FROM Source;

Concatenate(Data)
Load FieldValue('KEY',RecNo()) as KEY,
     '999' as CODE,
     'A' as CATEGORY,
     0 as VALUE
AutoGenerate FieldValueCount('KEY');
     

[francesctesi]

hi @Kushal_Chawda,

thanks it works!

Can I ask you please, how can I do it if instead of the default 'Value' '0', I  want to insert a corresponding value from another table (E.x: from a field called 'CountTotal'). Using the KEY field?

[PrashantSangle]

Hello, 

Try below

Test:
Load * Inline [
Key, Category, Code, Value
1, A, 123, 45
1, B, 222, 48
2, A, 123, 80
2, B, 555, 100
3, A, 123, 22
4, A, 123, 78
4, B, 222, 47
];

Join
Load Distinct Key,
'A' as Category,
'999' as Code,
'0' as Value
Resident Test;

[PrashantSangle]

check Applymap() or lookup()

[Kushal_Chawda]

@francesctesi  try below

Data:
Load KEY,
     CODE,
     CATEGORY,
     VALUE
FROM Source;

Concatenate(Data)
Load  KEY,
     '999' as CODE,
     'A' as CATEGORY,
     sum(CountTotal) as VALUE
resident anothertable
group by KEY;

if you have one to one relationship with KEY and CountTotal, no need to do sum and perform group by