Combining 4 fields to 1 field

[apthansh]

I have Date fields as below.I want to make them as one field.I don't want to do cross table.Can I just do by using If statement ?

M2MYear    FHYear     SurYear    PAYear

2019           2017          2015         2016

2018           2016          2012         2014

Tried below but doesn't work...any other method to do this please ?

Load

if(wildmatch(M2MYear,'*' )  ,M2MYear,

if(wildmatch(FHYear ,'*')    ,FHYear,

if(wildmatch(SurYear,'*')    ,SurYear,

if(wildmatch(PAYear,'*')      ,PAYear )))) as Year

Thanks much

[datagrrl]

From reading this formula, I think you are expecting to get the first non null value in this order. Is that correct?

[apthansh]

I am expecting all Year as one column.

The Year list will have 2012,2014,2015,2016,2017,2018,2019

[datagrrl]

Along these lines? So if you you selected 2012 from year, it would select any data value from 2012:

Canonical Date

[vishsaggi]

I doubt you can do that using If statement. can you try below:

Tab3:

LOAD M2MYear as Year, * INLINE [

M2MYear,FHYear,SurYear,PAYear

2019,2017,2015,2016

2018,2016,2012,2014

];

Concatenate

LOAD FHYear as Year

Resident Tab3;

Concatenate

LOAD SurYear as Year

Resident Tab3;

Concatenate

LOAD PAYear as Year

Resident Tab3;

Drop Fields FHYear,SurYear,PAYear;

[apthansh]

Thank you but that didnt work.Psee attacghed sample.When I select id 4012001 only one year gets selected instead it should select 2012,2015,2016.

sunnysunny‌stalwar1‌swuehl‌ aNY THOUGHTS PLEASE ?

[vishsaggi]

Yes because that is the only data you provided hence the solution for the same. I will look into your files and get back.

[vishsaggi]

Try this in your script:

Tab3:

LOAD

ReId as SpecialPrj_SavingsREIDKey,

year(mTMExpectedCompleteDate) as Year,

year(mTMExpectedCompleteDate) as mTMExpectedCompleteDate ,

  year(pSOExpectedCompleteDate) as pSOExpectedCompleteDate,

   year(SSExpectedCompleteDate) as SSExpectedCompleteDate,

    year(FFExpectedCompleteDate) as FFExpectedCompleteDate

FROM

[..\AnshTestFile.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate

LOAD SpecialPrj_SavingsREIDKey,

     pSOExpectedCompleteDate as Year

Resident Tab3

;

concatenate

LOAD SpecialPrj_SavingsREIDKey,

      SSExpectedCompleteDate as Year

Resident Tab3;

Concatenate

LOAD SpecialPrj_SavingsREIDKey,

     FFExpectedCompleteDate as Year

Resident Tab3;