Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Date quarter and year

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

 

Labels (1)
3 Replies
Highlighted
Partner
Partner

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

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor
Contributor

Thanks for Reply.

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

Can you please explain i detail.

Regards

 

 

Highlighted
Contributor
Contributor

Hi,

 

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

 

Regards,