- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Qlik Community MVP
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for Reply.
I didnt understood why if statement is used and where _Dummyquarter came from.
Can you please explain i detail.
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Also please find the attached revised logic which is the correct requirement.
Regards,