Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have Excel data set which I want to convert in Qlikview. I am open to suggestion but this is what I am thinking.
Data:
Country | City | Jan11 | Feb11 | ….. | Jan12 | Feb12 | …… | Jan13 | Feb13 |
US | New York | 100 | 20 | 420 | 317 | 37 | 137 | ||
Japn | Tokyo | 200 | 320 | 440 | 240 | 0 | 200 | ||
UK | London | 103 | 240 | 383 | 383 | 383 | 486 | ||
India | Mumbai | 140 | 280 | 140 | 0 | 0 | 140 |
Test:
CrossTable(Months, population,28 )
LOAD Country,
City,
concat(Date(AddMonths(Date(Today()),-2),'MMMM') & 'Year(Date(Today(), 'YYYYMMDD')-2)' ), /**JAN 11**/
...wil repeat the formula for every month.
but I know its not smartest way to achieve it.
Above report is Monthly. So every month will be added, and old month will be deleted. (e.g. when June 2013 data is available, May 2011 will be deleted)
can anyone help, what should I do? Some example will help.
First I should create a table with the fieldnames:
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
FieldNames:
LOAD 25-RowNo() as ID,Month(AddMonths(Today(),-RowNo()+1))&Right(AddMonths(Today(),-RowNo()+1),2) as FieldName,
Num(Month(AddMonths(Today(),-RowNo()+1))) as Month,
Year(AddMonths(Today(),-RowNo()+1)) as Year
AutoGenerate 24;
Then I should use this table to load your excel dinamically
I am not sure if I understood your problem exactly, but here is what i suggest from what i understand..
first, make sure how many months data you need to see in the chart at any given time...
if for example any time u need too see data for recent 6 months, you can probably use this
example latest date is 20130510
LET vMonth1= month(Date(MAX(Datefield)))&'-'& year(max(Datefield)) , this gives you May-2013
LET vMonth2=addmonths(Date(Max(Datefield))-1)&'-'year(admonths(max(Datefield),-1)), this gives you April-2013
.
.
.
LET vMonth6=addmonths(Date(Max(Datefield))-6)&'-'year(admonths(max(Datefield),-6)), this gives you Dec-2012
so when new date is in next month that is 20130610, still the $(vMonth1) gives you Jun-2013 and $(vMonth6) gives Jan-2013
hope this helps....
Eddy Sanchez wrote:
Then I should use this table to load your excel dinamically
Can you elobrate on that ? So I build the table. How do I load the data into it.
Will it be something like:
FieldNames:
Load Country
City
? ? ?
? ? ?
FROM
[Trend 2013.xlsx]
(ooxml, embedded labels, table is [Population Trend]);
I am not able to connect dots here. I am still newbee and learning my ways around Qlikview.
Not sure I understand what the problem is, but I would load the data this way:
TempData:
CrossTable(Months, population,2)
LOAD * From File.txt;
Data:
LOAD
Country,
City,
Date#(Months,'MMMYY') as Month /* interpret the Month field */
resident TempData;
Drop Table TempData;
Then you will have loaded the months with correct interpretation. If you want to number the months relative today's date, you can add a master calendar that does this:
Calendar:
Load distinct
Month,
12*Year(Month) + Month(Month) - 12*Year(Today()) - Month(Today()) as RelativeMonth
resident Data;
HIC