Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

smiling_cheetah
Contributor

Make a new field out of particular values in existing one

 

Hi everyone, 

 

I have a following data and having a brain lag with resolving the issue, would appreciate any help (sorry if the Subject name is misleading) 

The initial data looks like the following

FieldA, FieldB
01.01.1999
a, 10
b, 20
02.02.1999
c, 11
b, 21

The desirable outcome is 

FieldA, FieldB, DateField
a, 10, 01.01.1999
b, 20, 01.01.1999
c, 11, 02.02.1999
b, 21, 02.02.1999

What would be the easiest way to transform the table that way? 

Thank you in advance

Labels (2)
2 Replies
Partner
Partner

Re: Make a new field out of particular values in existing one

May be like this

Data:
Load * Inline [
FieldA, FieldB
01.01.1999
a, 10
b, 20
02.02.1999
c, 11
b, 21];

Data_Temp:
Load FieldA, FieldB, if(Len(DateField)<1,Peek(DateField),DateField)as DateField ;
Load FieldA, FieldB, if(len(FieldA)>9,FieldA) as DateField resident Data;
Drop Table Data;
Load FieldA, FieldB,DateField ,RecNo()Resident Data_Temp where len(FieldA)<9;
Drop Table Data_Temp;

 

Regard

Raman

Highlighted
smiling_cheetah
Contributor

Re: Make a new field out of particular values in existing one

Hi Raman, 

Thanks for the reply, 

I actually figured out the following option: 

Data_tmp:
Load *,
if(FieldA like '?*.?*.????', FieldA, peek(DateField)) as DateField 
Inline [ FieldA, FieldB 01.01.1999 a, 10 b, 20 02.02.1999 c, 11 b, 21];

And then just cleaning the table

Data:
NoConcatenate
Load * 
Resident Data_tmp
Where FieldA <> '?*.?*.????';

drop table Data_tmp;