Create New Column Header/Title - Depending on Column Availability.

[fikrizahari]

Hi,

Good day !

I would like to know whether there is tools / formula to create a new column title/header, if it cannot be found before.
The reason is : final output requires the show all column title.

Below is the example ( assume the table from client ) :

Employee Name	Monthly Salary	Wagetype	Amount
Angeline	4349.17	House Allowance	300
Bruce	7722.43	House Allowance	300
Charlotte	10483.33	House Allowance	300
Diana	7558.69	WFH Allowance	100
Eugene	10483.33	WFH Allowance	100
Fiore	14016.9	WFH Allowance	100
Glaze	14016.9	WFH Allowance	100
Heather	4158.33	WFH Allowance	100
Angeline	4349.17	Lunch Allowance	50
Bruce	7722.43	Lunch Allowance	50
Charlotte	10483.33	Dental Reimbursement	51
Charlotte	10483.33	Dental Reimbursement	71
Diana	7558.69	Dental Reimbursement	43
Eugene	10483.33	Dental Reimbursement	56

Currently, i only use 'Cross Tab' and 'Formula' tools to get below result:

Employee Name	Monthly Salary	Dental_Reimbursement	House_Allowance	Lunch_Allowance	WFH_Allowance	Annual Bonus	Phone Allowance
Charlotte	10483.33	122	300			0	0
Fiore	14016.9				100	0	0
Bruce	7722.43		300	50		0	0
Eugene	10483.33	56			100	0	0
Heather	4158.33				100	0	0
Diana	7558.69	43			100	0	0
Glaze	14016.9				100	0	0
Angeline	4349.17		300	50		0	0

The issue: there is possibility that the input file contain amount for 'Phone Allowance' and 'Annual bonus'.
In that case, I do not want the formula tools to create 'Phone Allowance' and 'Annual bonus'.

Thanks in advance!

[Luke_C]

Hi @fikrizahari 

Could you add a filter before the cross-tab to remove Phone Allowance and Annual Bonus values?

[martinding]

Hi @fikrizahari ,

You could try this approach:

And after the final step, it will be just a matter of how you'd like to deal with nulls.

You can leave them as they are, or use a Cleanse tool, or use an Imputation tool.

[flying008]

Hi, @fikrizahari 

The other way :