If condition and easier output method

[Ash_dsz]

Hi All,

Can someone please help me get the attached output from the attached input? I have added the sheet names respectively. I got an error on the below formula when i tried using the formula tool to segregate the period first.

IF [Period] = 1 then "1"

ELSEIF [Period]=2 then "2"

ELSEIF [Period]=3 THEN "3"

ELSEIF [Period] <=15 THEN "4-15"

ELSEIF [Period]<= 30 THEN "16-30"

ELSE ">30" ENDIF

[AngelosPachis]

Hi @Ash_dsz ,

Your formula worked for me. Are you sure you have defined a name for the new column you want to create?

Cheers,

Angelos

[apathetichell]

Is the field you are trying to put this formula in a numeric field and is [period] a number?

If [period] isn't a number you won't be able to compare it to 3.

[AngelosPachis · Accepted solution]

In general @Ash_dsz , you will face other issues further down the line.

For example, even if you go around the issue with the formula and you create the period group, then if you cross-tab straight away you will notice that not all columns are included in your output.

As you can see for yourself, column with period group 2 is not there, and that's because none of the records in your original dataset belongs to a period group of 2.

There is a way for you to go around that, by bringing in a text input tool with all the expected period groups (6 in number, 1,2, 3, 4-15, 16-30, 30+). Then if you join on period group with the main data stream, you will have missing period groups falling out of the L output anchor of the join.

So now you know that you are missing period group 2 in the final table, so you have to generate all type/name combinations before you feed it back to the main stream for the final cross tab to work.

A bit more complex than what you were thinking probably when you posted this question, but it works.

Hope that helps, let me know if that worked for you or you have any questions.

Cheers,

Angelos