Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friends
I'm building a report and I have the NEXT issue.
I have a date "Transaction date" used as a method to filter information. For example: "View all transactions in January," as shown below.
The only information I list when it was "stored" or where "there are changes" in one transaction day. What I need is to see the full performance of January a day, it does not matter if the number in column "AVG ON-HAND BALANCE" and "ANNUAL DEMAND repeat for every day that no changes have occurred. As shown below.
Thanks in advance !
Karen[:S]
jan-10 | Onhand balance | Annual Demand | RMT |
1 | 2 216 | 302016 | 136 |
2 | 2 216 | 302016 | 136 |
3 | 2 216 | 302016 | 136 |
4 | 2 216 | 302016 | 136 |
5 | 2 216 | 302016 | 136 |
6 | 2 216 | 302016 | 136 |
7 | 2 216 | 302016 | 136 |
8 | 2 216 | 302016 | 136 |
9 | 2 216 | 302016 | 136 |
10 | 2 216 | 302016 | 136 |
11 | 2 216 | 302016 | 136 |
12 | 2 216 | 302016 | 136 |
13 | 2 216 | 302016 | 136 |
14 | 2 216 | 302016 | 136 |
15 | 2 216 | 302016 | 136 |
16 | 2 216 | 302016 | 136 |
17 | 2 216 | 302016 | 136 |
18 | 2 216 | 302016 | 136 |
19 | 2 216 | 302016 | 136 |
20 | 2 216 | 302016 | 136 |
21 | 2 216 | 302016 | 136 |
22 | 2 216 | 302016 | 136 |
23 | 2 216 | 302016 | 136 |
24 | 2 216 | 302016 | 136 |
25 | 2 216 | 302016 | 136 |
26 | 2 216 | 302016 | 136 |
27 | 2 300 | 302016 | 131 |
28 | 2 300 | 302016 | 131 |
29 | 4 250 | 302016 | 71 |
30 | 4 250 | 302016 | 71 |
31 | 4 250 | 302016 | 71 |
Average | 2 418 | 302016 | 125 |
I do not know if the solution is to create a date variable and see if the records are within the range month for example .... I think I'm a little confused! besides I am new to this forum and QlikView!
Good morning Karen,
here are a modified version. I hope it point you in the right direction.
Good luck!
Rainer
Hi Karen,
not sure if I´m on the right track. See the example and let me know if your problem is likely the same.
See the script for details.
Good luck!
Rainer
Hi Rainer, thanks a lot for you speedy answer !!!
I think your code is very close to the solution to or desire, but this confused me a part and I hope you can explain it to me.
1. I succeed in creating the calendar without any problem and the listing information for each day of each month.
My problem is with this part of your code:
//*** Create a calendar and join with the original table
//*** Now the dates are complete
Join (Temp_PreData)
Load
MNR,
Date($(vMinDatum) + Iterno() - 1) as Datum
Resident Temp_PreData
While IterNo() <= $(vAnzahl) ;
PreData:
Load distinct
' ' as dummy,
Datum,
//*** Find dates without a status where employee number is the same as before
if(IsNull(Status) and MNR=peek(MNR),peek(Status),Status) as Status,
MNR
resident Temp_PreData
Order By MNR, Datum;
Drop Table Temp_PreData;
You represent MNR in the temporary table of dates, but in my case I would have that data in a table in my database, the same Status.
So I have problems to do the join with a table that you named in the charge as "TempActual" has the following format:
Temp_PreData:
LOAD * INLINE [Year, Month, Day
2010,01,1
2010,01,1
2010,01,2
2010,01,3
2010,01,4
2010,01,5
2010,01,6 ;
Load
Min(Day) as MinDay,
Max(Day) as MaxDay
Resident Temp_PreData;
Let vMinDay = Peek('MinDay', 0, 'MinMaxDay');
Let vMaxDay = Peek('MaxDay', 0, 'MinMaxDay');
Let vAnzahl = $(vMaxDay) - $(vMinDay) +1;
TempActual:
LOAD
MTSTNO AS [Total on-hand balance], (FOR YOU STATUS)
MTTRDT AS [Transaction date]; (FOR YOU DATUM)
SELECT *
FROM $(DataBase).TABLE;
If you can give to me one hand with the join.... I am very grateful !
One Hug
Karen
Hi Karen,
is it possible for you to upload a small qv example i can play around with?
It´s helpfull for me to have the same kind of data as you.
Thank you!
Rainer
Hi Rainer !!! 🙂
I attach one file, I hope this can help you understand my problem.
" Helping you to help me"
Many greetings!
Karen
Karin,
I´ll come back to you tomorrow.
Have a nice evening!
Rainer
Hi Rainer,
Thankssssssssssssssss !!!
i here whaiting hehehe
Have nice evening also.
Karen
Good morning Karen,
you wil find some comments in the example to follow the steps needed to solve your problem.
It´s as always one way to reach the goal.
Best wishes and have a wonderful weekend!
Rainer
Dear Rainer, Good Morning
Your code works wonderfully !!!!. BUT, I now have a little problem with the configuration of the date, as I want, give the option select by year or by month or by day, so I like to "split" the date, since currently only and full board is listed in the format yearmonthday is not optimal if a user wants to select the entire month. You understand me?
I have tried to format the variable "Transaction_date" with the following code:
Year (Date#(Num(Transaction_date),'YYYYMMDD'))&Num(Month(Date#(Num(Transaction_date),'YYYYMMDD')),00) AS YearMonth,
Year(Date#(Num(Transaction_date),'YYYYMMDD')) as Year,
Month(Date#(Num(Transaction_date),'YYYYMMDD')) as Month,
Week(Date#(Num(Transaction_date),'YYYYMMDD')) as Week,
Day(Date#(Num(Transaction_date),'YYYYMMDD')) as Day,
WeekDay(Date#(Num(Transaction_date),'YYYYMMDD')) as WeekDay
But not responding.
Thank you very much for all the help you gave me these days!
A hug and a very good start this week for you.
Karen
Dear Rainer, Good Morning
Your code works wonderfully !!!!. BUT, I now have a little problem with the configuration of the date, as I want, give the option select by year or by month or by day, so I like to "split" the date, since currently only and full board is listed in the format yearmonthday is not optimal if a user wants to select the entire month. You understand me?
I have tried to format the variable "Transaction_date" with the following code:
Year (Date#(Num(Transaction_date),'YYYYMMDD'))&Num(Month(Date#(Num(Transaction_date),'YYYYMMDD')),00) AS YearMonth,
Year(Date#(Num(Transaction_date),'YYYYMMDD')) as Year,
Month(Date#(Num(Transaction_date),'YYYYMMDD')) as Month,
Week(Date#(Num(Transaction_date),'YYYYMMDD')) as Week,
Day(Date#(Num(Transaction_date),'YYYYMMDD')) as Day,
WeekDay(Date#(Num(Transaction_date),'YYYYMMDD')) as WeekDay
But not responding.
Thank you very much for all the help you gave me these days!
A hug and a very good start this week for you.
Karen