Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
First post so forgive the dumb question.
I have data with values for for some dates, but I need to calculate a mean. So I need to show all dates and show a zero value for the dates that have no activity.
For example, this is what I have:
ItemNo | DateUsed | QtyUsed |
30 | 1/1/2014 | 1000 |
30 | 1/3/2014 | 1200 |
30 | 1/4/2014 | 1330 |
30 | 1/6/2014 | 1469 |
20 | 1/2/2014 | 1176 |
20 | 1/3/2014 | 1376 |
20 | 1/5/2014 | 1447 |
This is what I would like my script to generate (assuming I wanted all dates between 1/1/2014-1/7/2014):
ItemNo | DateUsed | QtyUsed |
30 | 1/1/2014 | 1000 |
30 | 1/2/2014 | 0 |
30 | 1/3/2014 | 1200 |
30 | 1/4/2014 | 1330 |
30 | 1/5/2014 | 0 |
30 | 1/6/2014 | 1469 |
30 | 1/7/2014 | 0 |
20 | 1/1/2014 | 0 |
20 | 1/2/2014 | 1176 |
20 | 1/3/2014 | 1376 |
20 | 1/4/2014 | 0 |
20 | 1/5/2014 | 1447 |
20 | 1/6/2014 | 0 |
20 | 1/7/2014 | 0 |
I would like to generate this data table in the script. So far I've been able to create a calendar file which contains all dates, but I can't quite figure out how to create a record for all dates for each ItemNo.
Any help would be appreciated.
swuehl - I ended up using your first script example. The DISTINCT qualifier was necessary. Embarrassingly, the only thing that I needed to add was a GROUP BY statement on the initial data load. Here is what I ended up with:
INPUT:
LOAD
ItemNo,
DateUsed,
Sum(QtyUsed) as QtyUsed
FROM
C:\DataSource.xlsx
(ooxml, embedded labels, table is Sheet2)
GROUP BY ItemNo, DateUsed;
CALENDAR:
LOAD Date(makedate(2014)+recno()-1) as DateUsed AutoGenerate 7;
//Date values per ItemNo
JOIN (CALENDAR) LOAD Distinct ItemNo Resident INPUT;
//DROP Table INPUT;
//complete with known QtyUsed values per date and ItemNo
LEFT JOIN (CALENDAR) LOAD * Resident INPUT;
DROP Table INPUT;
//Clean up
RESULT:
NoConcatenate LOAD
ItemNo,
DateUsed,
RangeSum(QtyUsed) as QtyUsed
Resident CALENDAR
Order By ItemNo, DateUsed;
DROP Table CALENDAR;
Thanks again to everyone for the help!
Hey Ryan,
This isn't a dumb question but rather complicated. I am completely stumped as how to do this.
I found a few topics on it searching this site such as Missing values on time dimension but hopefully someone here will post an easier solution.
Best of Luck!
You can do it like this:
Set DateFormat = 'M/D/YYYY';
//Load your input table
INPUT:
LOAD ItemNo,
DateUsed,
QtyUsed
FROM
[http://community.qlik.com/thread/106041]
(html, codepage is 1252, embedded labels, table is @1);
//Generate complete date table
CALENDAR:
LOAD Date(makedate(2014)+recno()-1) as DateUsed AutoGenerate 7;
//Date values per ItemNo
JOIN (CALENDAR) LOAD DISTINCT ItemNo Resident INPUT;
//complete with known QtyUsed values per date and ItemNo
LEFT JOIN (CALENDAR) LOAD * Resident INPUT;
drop table INPUT;
//Clean up (order, fill in zero values)
RESULT:
NoConcatenate LOAD
ItemNo,
DateUsed,
rangesum(QtyUsed) as QtyUsed
Resident CALENDAR order by ItemNo, DateUsed;
drop table CALENDAR;
SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';
// generate date
Let vMinDate = num('1/1/2014');
Let vMaxDate = num('1/7/2014');
TRACE $(vMinDate) TRACE $(vMaxDate);
Date:
Load Date(recno()+$(vMinDate)-1) as DateUsed
Autogenerate vMaxDate - vMinDate ;
// source data
Source:
LOAD * INLINE [
ItemNo, DateUsed, QtyUsed
30, 1/1/2014, 1000
30, 1/3/2014, 1200
30, 1/4/2014, 1330
30, 1/6/2014, 1469
20, 1/2/2014, 1176
20, 1/3/2014, 1376
20, 1/5/2014, 1447
];
// cartesian ItemNo x Date
Tmp: load Distinct ItemNo Resident Source;
join (Date) load ItemNo Resident Tmp;
DROP Table Tmp;
// join to add QtyUsed
Left join (Date) load ItemNo, DateUsed, QtyUsed resident Source;
DROP Table Source;
// end
Table:
noconcatenate load ItemNo, DateUsed, alt(QtyUsed,0) as QtyUsed Resident Date;
DROP Table Date;
exit script;
How to do this is described in Generating Missing Data In QlikView
HIC
It similar to "Example: Populating a table with warehouse balances" within the document correct?
Hi Ryan, Try like below:
Temp:
LOAD * Inline [
ItemNo, DateUsed, QtyUsed
30, 1/1/2014, 1000
30, 1/3/2014, 1200
30, 1/4/2014, 1330
30, 1/6/2014, 1469
20, 1/2/2014, 1176
20, 1/3/2014, 1376
20, 1/5/2014, 1447
];
Join (Temp)
LOAD ItemNo , MinDate + IterNo() - 1 AS DateUsed While IterNo() <= MaxDate - MinDate +1 ;
LOAD ItemNo , Min(DateUsed) AS MinDate , Max(DateUsed) As MaxDate Resident Temp Group By ItemNo;
FINAL:
NoConcatenate
LOAD ItemNo ,
DateUsed ,
RangeSum(QtyUsed) AS QtyUsed
Resident Temp ;
Drop Table Temp;
Please find the attached file for reference.
First, thank you all for your replies.
I tried swuehl's solution first just b/c it was first in the list. It worked for my sample data. However when I applied it to my real data set I realized that I had some duplicate (but legit) values which were only being pulled in once.
New example:
ItemNo | DateUsed | UsedQty |
30 | 1/1/2014 | 1000 |
30 | 1/3/2014 | 1200 |
30 | 1/4/2014 | 1330 |
30 | 1/6/2014 | 1469 |
30 | 1/6/2014 | 1469 |
20 | 1/2/2014 | 1176 |
20 | 1/3/2014 | 1376 |
20 | 1/5/2014 | 1447 |
Notice the highlighted items. When I use swuehl's script, the table that I end up with in QV has one record for 1/6/2014 (which is what I want), but the UsedQty value is only 1469. I need it to be twice that (2938). I thought that the RangeSum would handle that, but I realized that the INPUT table is actually only pulling the 1469 to begin with.
I imagine that there is some small tweak that I need to make to the script, but I haven't been able to figure it out just yet. I will also try to apply Massimo and dathu's solution - though they look very similar to swuehl's. I will also check out the pdf which was linked by Henric.
Again, thanks for all of the replies. If any of you can see quickly off hand the reason for my problem, feel free to let me know!
Instead of a Table Box, make a straight table. Use ItemNo and DateUsed as you dimensions and Sum(UsedQty) as your expression.
My use of the DISTINCT qualifier may prevent the duplicate records from be loaded here.
Try maybe the attached modification.