topic Re: Generate missing date for exchange date in QlikView

Generate missing date for exchange date

paulyeo11 — Wed, 31 Jul 2013 07:43:46 GMT

Title :- How to fill my exchange rate date for the month of Jan 2013

Hi All

I have make use of excel table to input monthly exchange rate into QV. for example 1 Jan 2013 rate = 2.1

But my invoice date for Jan 2013 have many date. not just 1 Jan 2013.

My question is how to make my exchange rate fill all invoice date ?

Rainer recommend me to look at page 7 of below doc :-

http://community.qlik.com/people/hic?view=documents

Below is the code should work :-

TempTable_Rates:

Load date, EXCHANGE from sales_table;

MinMaxDate:

Load Min(Date) as MinDate,Max(Date)as MaxDate resident TempTable_Rates;

Let vMinDate=Peek('MinDate',-1,'MinMaxDate')-1;

Let vMaxDate=Peek('MaxDate',-1,'MinMaxDate');

Drop Table MinMaxDate;

Join(TempTable_Rate)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

But i am stuck with first 2 line of the script and cannot move on.

Hope some one can advise me.

Paul

Re: Generate missing date for exchange date

kaushiknsolanki — Wed, 31 Jul 2013 08:12:42 GMT

Hi,

Have a look at the example attached.

The logic is, you join your master cal with the exchange table with monthstart date. So when ever you select the date from master cal of that month, you will get the exchange rate.

Regards,

Kaushik Solanki

Re: Generate missing date for exchange date

jagan — Wed, 31 Jul 2013 08:18:34 GMT

Hi,

Please find below URL, hope it helps you.

http://community.qlik.com/docs/DOC-3858

Regards,

Jagan.

Re: Generate missing date for exchange date

paulyeo11 — Wed, 31 Jul 2013 08:22:50 GMT

Hi Kau

Many thank for your help. with your sample using my data , it help me understand a lot. now i should work back ward to add my sales order data. reservese engineering.

Paul

Re: Generate missing date for exchange date

paulyeo11 — Wed, 31 Jul 2013 08:27:10 GMT

Hi Jagan

Thank you for the link.

In my org QV doc i already implemented master calendar script as you share i in the link.

May i know how what is the different between your and below ? it is identical ?

TempTable_Rates:

Load date, EXCHANGE from sales_table;

MinMaxDate:

Load Min(Date) as MinDate,Max(Date)as MaxDate resident TempTable_Rates;

Let vMinDate=Peek('MinDate',-1,'MinMaxDate')-1;

Let vMaxDate=Peek('MaxDate',-1,'MinMaxDate');

Drop Table MinMaxDate;

Join(TempTable_Rate)

Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

i look thru both look quite the same to me , because it have min and max date.

just confuse.

Paul