Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
durga_bi13
Contributor
Contributor

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

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

Labels (1)
9 Replies
Taoufiq_Zarra

can you share an example ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
durga_bi13
Contributor
Contributor
Author

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;
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

 
Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
durga_bi13
Contributor
Contributor
Author

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

muthukumar77
Partner
Partner

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;

 

 

Muthukumar Pandiyan
durga_bi13
Contributor
Contributor
Author

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
Partner
Partner

Hi,

Can you explain your req clearly?

 

Muthukumar Pandiyan
durga_bi13
Contributor
Contributor
Author

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.