Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Create Key with Code and YearMonth

Hello,

I have the following table where DATE is MM/DD/YYYY:

CODEDATEQTY
11105/05/201730
11105/03/201755
33308/03/201718

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:

KEYQTY
111|20170585
333|20170818

Do you know how could I do that?

Note: I should have a single KEY value with summarized QTY.

Thank you!!!

1 Solution

Accepted Solutions
Highlighted

Re: Create Key with Code and YearMonth

This is what you need:

aaa:

LOAD * Inline [

CODE, DATE, QTY

111, 05/05/2017, 30

111, 05/05/2017, 55

333, 08/03/2017, 18

];

BBB:NoConcatenate

LOAD CODE & DATE as KEY, Sum(QTY) as QTY Resident aaa Group By CODE,DATE;

DROP Table aaa;

View solution in original post

6 Replies
Highlighted
Champion
Champion

Re: Create Key with Code and YearMonth

Hi,

you need to create a Month field from your date field and make a group by on it

Highlighted

Re: Create Key with Code and YearMonth

This is what you need:

aaa:

LOAD * Inline [

CODE, DATE, QTY

111, 05/05/2017, 30

111, 05/05/2017, 55

333, 08/03/2017, 18

];

BBB:NoConcatenate

LOAD CODE & DATE as KEY, Sum(QTY) as QTY Resident aaa Group By CODE,DATE;

DROP Table aaa;

View solution in original post

Highlighted
Creator III
Creator III

Re: Create Key with Code and YearMonth

Try this :

Temp:

LOAD * INLINE [

CODE,DATE,QTY

111,05/05/2017,30

111,05/03/2017,55

333,08/03/2017,18

];

NoConcatenate

Temp_2:

Load

CODE,

text(Date(Date#(DATE,'MM/DD/YYYY'),'YYYYMM')) as DATE,

QTY

Resident Temp;

Drop Table Temp;

NoConcatenate

Final:

Load

CODE&'|'&DATE as KEY,

sum(QTY) as QTY

Resident Temp_2

Group by CODE,DATE;

Drop Table Temp_2;

Highlighted
Champion
Champion

Re: Create Key with Code and YearMonth

Hi Alessandro,

his first two lines are different on the Day part..

so you can't group by these columns, right ?

Highlighted

Re: Create Key with Code and YearMonth

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

Highlighted
Creator III
Creator III

Re: Create Key with Code and YearMonth

Try this :

Temp:

LOAD * INLINE [

CODE,DATE,QTY

111,05/05/2017,30

111,05/03/2017,55

333,08/03/2017,18

];

NoConcatenate

Temp_2:

Load

CODE,

text(Date(Date#(DATE,'MM/DD/YYYY'),'YYYYMM')) as DATE,

QTY

Resident Temp;

Drop Table Temp;

NoConcatenate

Final:

Load

CODE&'|'&DATE as KEY,

sum(QTY) as QTY

Resident Temp_2

Group by CODE,DATE;

Drop Table Temp_2;