Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Combining 4 fields to 1 field

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

7 Replies
datagrrl
Creator III
Creator III

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

apthansh
Creator
Creator
Author

I am expecting all Year as one column.

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

datagrrl
Creator III
Creator III

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

Canonical Date

vishsaggi
Champion III
Champion III

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
Creator
Creator
Author

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.

sunnysunnystalwar1swuehl‌ aNY THOUGHTS PLEASE ?

vishsaggi
Champion III
Champion III

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
Champion III
Champion III

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;