Create new column when loading data if a value in the column fulfil a certain condition

[Poppel]

Hi,

I have a sheet looking like this:

Region | People| Number

JAPAN          Boy             10

JAPAN          Girl             20

JAPAN          Total           30

Is it possible to create a new column when loading the sheet to make the result look like this:

Region | People| Number| Total_Number

JAPAN      Boy             10                30

JAPAN       Girl             20                30

I was thinking something like :  IF([People], 'Total') AS [Total Number], but it didn't work.

Cheers,

Poppel

[Ezir]

Hi @Poppel 

If this new column is an aggregation I suggest creating expression in dashboard.

To tell you the truth... I don't really fully understand the issue at hand.... If you can give another example🤔

[jatishqv · Accepted solution]

Hi 

You can try this.

Data_Temp_1:
LOAD * INLINE [
    Region, People, Number
    JAPAN, Boy, 10
    JAPAN, Girl, 20
    JAPAN, Total, 30
];

Data:
NoConcatenate
LOAD Region,
	 People,
	 Number
Resident Data_Temp_1
Where People <> 'Total';

Join (Data)
LOAD Region,
	 Number as Total_Number
Resident Data_Temp_1
Where People = 'Total';

Drop Table Data_Temp_1; 

[Poppel]

Hi @jatishqv 

Made it. Not with the exact syntax but with the same logic.

Thanks bunch! 🌻