Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
durga_bi13
New 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
Highlighted
Taoufiq_ZARRA
Valued Contributor

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

can you share an example ?


Taoufiq ZARRA
Highlighted
durga_bi13
New Contributor

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

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

Highlighted
Taoufiq_ZARRA
Valued Contributor

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

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;

Taoufiq ZARRA
Highlighted
Taoufiq_ZARRA
Valued Contributor

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

 

Taoufiq ZARRA
Highlighted
durga_bi13
New Contributor

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

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

Highlighted
Partner
Partner

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

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
Highlighted
durga_bi13
New Contributor

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

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

Highlighted
Partner
Partner

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

Hi,

Can you explain your req clearly?

 

Muthukumar Pandiyan
Highlighted
durga_bi13
New Contributor

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

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.