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

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

durga_bi13 — Sat, 16 Nov 2024 01:22:54 GMT

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

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

Taoufiq_Zarra — Fri, 31 Jan 2020 10:57:03 GMT

can you share an example ?

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

durga_bi13 — Fri, 31 Jan 2020 11:11:50 GMT

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

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

Taoufiq_Zarra — Fri, 31 Jan 2020 12:17:13 GMT

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;

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

Taoufiq_Zarra — Fri, 31 Jan 2020 12:18:01 GMT

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

durga_bi13 — Fri, 14 Feb 2020 07:28:50 GMT

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

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

muthukumar77 — Fri, 14 Feb 2020 07:49:45 GMT

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;

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

durga_bi13 — Fri, 14 Feb 2020 08:12:17 GMT

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

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

muthukumar77 — Fri, 14 Feb 2020 08:34:36 GMT

Hi,

Can you explain your req clearly?

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

durga_bi13 — Tue, 18 Feb 2020 11:19:03 GMT

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.