If condition and easier output method

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

input and output.xlsx

Workflow

Connectors

Dynamic Processing

Predictive Analysis

Accepted answers

AngelosPachis

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

example.yxmd

All comments

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

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.

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

example.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703