from min - max date to one date with conditions

[#1]

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?

[its_anandrjs]

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);

[#3]

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

[its_anandrjs]

i am not understand your last point can you explain with an example.

[its_anandrjs]

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.

[#6]

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.

[#7]

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

[its_anandrjs]

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.

[#9]

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

[its_anandrjs]

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