Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
as a new Qlik'er today I have a problem with date.
I have a few Excel files named like this: statistic_01012012_03312012.xlsx
I defined my data with:
Date(Date#(SubField(FileBaseName(),'_',2),'DDMMYYYY'),'DD.MM.YYYY') as MinDate,
Date(Date#(SubField(FileBaseName(),'_',3),'MMDDYYYY'),'DD.MM.YYYY')as MaxDate,
But now I need to have a new column fill with one date (every day between Min and Max Date, include Min, Max ) but in case of number of rows (it's change dynamically, depends on excel file)
Does anyone know how to solve this problem?
Load Your table like
LOAD
Cola,
FileBaseName() as Name,
Date(Date#(SubField(FileBaseName(),'_',2),'DDMMYYYY'),'DD.MM.YYYY') as MinDate,
Date(Date#(SubField(FileBaseName(),'_',3),'MMDDYYYY'),'DD.MM.YYYY')as MaxDate
FROM
(ooxml, embedded labels, table is Sheet1);
I load my tabele like that:
Table:
LOAD Date(Date#(SubField(FileBaseName(),'_',2),'DDMMYYYY'),'DD.MM.YYYY') as MinData,
Date(Date#(SubField(FileBaseName(),'_',3),'MMDDYYYY'),'DD.MM.YYYY')as MaxData,
page,
xxx,
[ccc ddd],
aaa
FROM
statistic_01012012_03312012.xlsx
(ooxml, embedded labels, table is Products);
but I need to do something like that:
If in table product will be for example 90 rows then
01.01.2012 - 31.03.2012 (this time) i want to divide to 90 (number of rows) and my date colum will look like this below:
01.01.2012 |
02.01.2012 |
… |
31.03.2012 |
If in table product will be for example 180 rows then
01.01.2012 |
01.01.2012 |
02.01.2012 |
02.01.2012 |
… |
31.03.2012 |
31.03.2012 |
As you can see I want to have one column with date with depends of number of products
i am not understand your last point can you explain with an example.
Line you want to assign the dates from mindate to maxdate to the rows
Mindate 01.01.2012
Maxdate 31.03.2012
Eg:-
page, xxx, [ccc ddd], aaa, 01.01.2012
..
..
..
page, xxx, [ccc ddd], aaa, 31.03.2012
means 90 rows but how you can assign date is there any related column like date field.
If there is any date field then connect it with master calender dates.
Let me know.
I've attached my source file, and what i Want to have.
I will have a few files always named statistic_DateFrom_DateTo.xlsx like in example,
So I know start and end date of this data (there are some statistic from number of searching on page)
I don't know how many data will be in next period of time.
I want to create a new colum and fill them by Date. So I must divide this period of time.
It's difficult because regardless of number of data field (90, 160 or 1000) I must attribute them succeding dates.In example of 90 days it's day by day, but if I will have more records, days should be repeated. In case of 1000 records first date will reapeat around 11 times (in field 1 -11), second date 11 times...(12-23) last date (perhaps 10) etc..
I can't explain better than I must almost equally divide period of time from file name to number of rows and join that values with my table
Load your table like
Temp:
LOAD
RowNo() as Rowid,
Date(Date#(SubField(SubField(FileBaseName(),' ',2),'-',1),'DD.MM.YYYY'),'DDMMYYYY') AS MinDate,
Date(Date#(SubField(SubField(FileBaseName(),' ',2),'-',2),'DD.MM.YYYY'),'DDMMYYYY') AS MaxDate,
Page,
[No of visit],
Unique,
aaddd,
Page&'_'&[No of visit]&'_'&Unique&'_'&aaddd as Key
FROM
(ooxml, embedded labels, table is Source);
LET vMin = num(Peek('MinDate',0,'Temp'));
LET vMax = num(Peek('MaxDate',-1,'Temp'));
Left Join
Date:
LOAD
RowNo() as Rowid,
date( $(vMin)+IterNo() - 1) as Date
AutoGenerate 1
While $(vMin)+IterNo() <= $(vMax);
When you join this 2 tables the data you get is depend on the no of rows which is exactly same with the Date table which is joining to the Temp table.
Thank You very much for helpful answer. Maybe is not exactly what I want, beacuse I want number of dates equal to number of data in ascending order. But I increase Autogenerate number and now every row has date.
Thanks a lot once more time
Yes you are right expected this problem for this add one more variable like No Of Rows
Like
LET vNoOfrows = NoOfRows('Temp');
Temp:
LOAD
RowNo() as Rowid,
Date(Date#(SubField(SubField(FileBaseName(),' ',2),'-',1),'DD.MM.YYYY'),'DDMMYYYY') AS MinDate,
Date(Date#(SubField(SubField(FileBaseName(),' ',2),'-',2),'DD.MM.YYYY'),'DDMMYYYY') AS MaxDate,
Page,
[No of visit],
Unique,
aaddd,
Page&'_'&[No of visit]&'_'&Unique&'_'&aaddd as Key
FROM
(ooxml, embedded labels, table is Source);
LET vMin = num(Peek('MinDate',0,'Temp'));
LET vMax = num(Peek('MaxDate',-1,'Temp'));
LET vNoOfrows = NoOfRows('Temp'); // Add this variable <<----
Left Join
LOAD
RowNo() as Rowid,
date( $(vMin)+IterNo() - 1) as Date
AutoGenerate $(vNoOfrows)
While $(vMin)+IterNo() <= $(vMax);
Regards