Discussion board where members can get started with QlikView.
I have the following table where DATE is MM/DD/YYYY:
What I need is to create a field named KEY to SUM by Script (with Group By) each combination with CODE and DATE (YearMonth):
So I should get the following:
Do you know how could I do that?
Note: I should have a single KEY value with summarized QTY.
This is what you need:
LOAD * Inline [
CODE, DATE, QTY
111, 05/05/2017, 30
111, 05/05/2017, 55
333, 08/03/2017, 18
LOAD CODE & DATE as KEY, Sum(QTY) as QTY Resident aaa Group By CODE,DATE;
DROP Table aaa;
View solution in original post
you need to create a Month field from your date field and make a group by on it
Try this :
LOAD * INLINE [
text(Date(Date#(DATE,'MM/DD/YYYY'),'YYYYMM')) as DATE,
Drop Table Temp;
CODE&'|'&DATE as KEY,
sum(QTY) as QTY
Group by CODE,DATE;
Drop Table Temp_2;
his first two lines are different on the Day part..
so you can't group by these columns, right ?
Yes, you are right, I changed the date because I thing there wa a mistake, infact the result you need works with changed date.
If dates are really differen maybe you need aggragation by Code and Year+Month date?
Let me know