topic Re: Combining 4 fields to 1 field in QlikView

Combining 4 fields to 1 field

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Combining 4 fields to 1 field

datagrrl — Mon, 17 Sep 2018 19:27:16 GMT

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

Re: Combining 4 fields to 1 field

apthansh — Mon, 17 Sep 2018 19:28:45 GMT

I am expecting all Year as one column.

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

Re: Combining 4 fields to 1 field

datagrrl — Mon, 17 Sep 2018 19:32:53 GMT

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

Canonical Date

Re: Combining 4 fields to 1 field

vishsaggi — Mon, 17 Sep 2018 20:03:39 GMT

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;

Re: Combining 4 fields to 1 field

apthansh — Tue, 18 Sep 2018 15:47:38 GMT

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 ?

Re: Combining 4 fields to 1 field

vishsaggi — Tue, 18 Sep 2018 15:52:02 GMT

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

Re: Combining 4 fields to 1 field

vishsaggi — Tue, 18 Sep 2018 15:56:34 GMT

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;