topic Re: Date quarter and year in QlikView

Date quarter and year

anisha_nan — Sat, 16 Nov 2024 04:13:31 GMT

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

Re: Date quarter and year

Vegar — Mon, 11 Mar 2019 10:55:01 GMT

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

Re: Date quarter and year

anisha_nan — Tue, 12 Mar 2019 10:54:41 GMT

Thanks for Reply.

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

Can you please explain i detail.

Regards

Re: Date quarter and year

anisha_nan — Tue, 12 Mar 2019 11:18:37 GMT

Hi,

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

Regards,