Date quarter and year

[anisha_nan]

Hi,

I have two excel sheet in which i have a data of dates as :

For Eg: Excel 1 :

Code,[q1 2016], [q2 2016],[q3 2016],[q4 2016],2017

1000001,110,210,123,345,456

For Eg: Excel 2 :

ID, [q1 2017], [q2 2017],[q3 2017],[q4 2017],2018

1111112,110,210,123,345,456

Above has the two excel sheet in which we have as quarter wise data and in some case we have only year wise data no quarter.

Now we have to capture both the scenario in qlik, it should pick the data where it has quarter wise data as well as if in the excel there is no quarter wise data for that particular year and has only year wise data then it should pick that particular year data value from it.

can anybody help how to handle both the scenario in qlik.

Attached word doc for more detail.

Regards

[Vegar]

Try this code.

DATA:
CrossTable  (DummyPeriod, Value)
LOAD * INLINE [
Code,q1 2016, q2 2016,q3 2016,q4 2016,2017
1000001,110,210,123,345,456];

CrossTable (DummyPeriod, Value)
LOAD * INLINE [
Code, q1 2017, q2 2017,q3 2017,q4 2017,2018
1111112,110,210,123,345,456
];

FinalData:
LOAD  
	Code,
	Year,
	if(_DummyQuarter>0,'Q'&_DummyQuarter) as Quarter,
	Value;
LOAD 
	*, 
	subfield(DummyPeriod, ' ' , -1) as Year,
	match(subfield(DummyPeriod, ' ', 1),'q1','q2', 'q3', 'q4') as _DummyQuarter 
Resident DATA;
DROP TABLE DATA;

It will generate this table

[anisha_nan]

Thanks for Reply.

I didnt understood why if statement is used and where _Dummyquarter came from.

Can you please explain i detail.

Regards

[anisha_nan]

Hi,

Also please find the attached revised logic which is the correct requirement.

Regards,