How to load qvd data based on excel field start date an end date field

[durga_bi13]

i need to load qvd file based on date interval available in excel 

[Taoufiq_Zarra]

can you share an example ?

[durga_bi13]

i have one qvd file with product number,name,sales date 

and another excel sheet with start date and end date and product number.

now i want to load only data of qvd file with salesdata in between of start date and end date of excel sheet

[Taoufiq_Zarra]

attached :

QVD file ->product number,name,sales date 

Excel ->start date and end date and product number

Qlikview file ->oad only data of qvd file with salesdata in between of start date and end date of excel sheet

StartEnd:
LOAD [product number] as Pn, 
     Date([start date])as Sd, 
     Date([end date])as Ed
     
FROM
.\qlik_1671252.xlsx
(ooxml, embedded labels, table is Feuil1);



FOR i = 0 to NoOfRows('StartEnd')-1

LET Startdate = Date(peek('Sd', $(i), 'StartEnd'));

LET Enddate= Date(peek('Ed', $(i), 'StartEnd'));

LET ProductNumber= peek('Pn', $(i), 'StartEnd');


Data: 

LOAD [product number], 
     name, 
     Date([sales date]) as [sales date]
FROM
.\qvd_1671252.qvd (qvd)
where [product number]='$(ProductNumber)' and [sales date]>='$(Startdate)' and [sales date]<='$(Enddate)';


NEXT i


drop table StartEnd;

[durga_bi13]

Thank you. but because of for loop it takes more time to load

[muthukumar77]

Hi,

use like this,

DateFilter:
Load [product number]&Num(Date) as Date_Filter;
LOAD [product number],
IterNo() as Day,
Date( [start date] + IterNo() - 1 ) as Date
While [start date] + IterNo() - 1 <= [end date];
LOAD [product number],
Date([start date]),
Date([end date])
FROM
.\qlik_1671252.xlsx
(ooxml, embedded labels, table is Feuil1);

Data:
LOAD [product number],
name,
Date([sales date]) as [sales date]
FROM
.\qvd_1671252.qvd (qvd)
where Exists(Date_Filter,[product number]&Num(date([sales date])));

Drop Table DateFilter;

[durga_bi13]

SalesInvoice:
load *,
[ProductNumber] & Num([SalesDate]) as Promo_Key
From
[........\SALES.Qvd]
(QVD);

Program:
LOAD [Product Name],
[Product Number],
[Start Date],
[End Date],
Year([Start Date]) as StartYear,
Year([End Date]) as EndYear,
Date([Start Date] + IterNo() - 1) as TempDate,
[Item Number] & Num([Start Date] + IterNo() - 1) as Promo_Key
While [Start Date] +IterNo()-1<=[End Date];

Load *
FROM
$(vExcelDocuments)\$(PromoProgram_Data)
(ooxml, embedded labels, table is Sheet1);

but right now my date filter of -7 days, -21 days not working now

[muthukumar77]

Hi,

Can you explain your req clearly?

[durga_bi13]

i have date filter like start date and end date

For Start Date filters are like -7 days and  -21 days

For End date as +7 days and +21 days

I have a straight table which contains Product id and year basis sales like sales date, and 2017 sales,2018 sales,2019 sales.

In the straight table sum of year based sales are calculated based on sales date between start and end date.

My expression in straight table

sum({<StartYear ={'$(vLY2)'},TempDate = {"<=$(=Date(max({<StartYear={'$(vLY2)'}>}[End Date])+$(vEnd)))>=$(=Date(min({<StartYear={'$(vLY2)'}>}[Start Date])-$(vStart)))"}>}Sales)

start year- year of start datae

vLY2- Today()-2

for filter '-7',  i have three actions

select in field -field name is  [Sales Date] , search in string is   =if(YearPeriod= StartYear,date(min([Start Date])-7))

set variable variable- vStart,  value = 7

select in field - Field is negative which is created on inline basis with value -7

In my straight table i get year basis sales between start and end date based on year

if my default min (start date) is 1/1/2018 and my end date with 2/1/2018  it displays records in straight table with this interval for each product

on clicking to -7 , i need to get sales from 12/25/2017. so the sum of values need to get added in existing year based sales.