topic Re: Create Key with Code and YearMonth in QlikView

Create Key with Code and YearMonth

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

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

CODE	DATE	QTY
111	05/05/2017	30
111	05/03/2017	55
333	08/03/2017	18

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:

KEY	QTY
111\|201705	85
333\|201708	18

Do you know how could I do that?

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

Thank you!!!

Re: Create Key with Code and YearMonth

YoussefBelloum — Thu, 01 Mar 2018 15:46:08 GMT

Hi,

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

Re: Create Key with Code and YearMonth

alexandros17 — Thu, 01 Mar 2018 15:46:53 GMT

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;

Re: Create Key with Code and YearMonth

jaumecf23 — Thu, 01 Mar 2018 15:49:54 GMT

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;

Re: Create Key with Code and YearMonth

YoussefBelloum — Thu, 01 Mar 2018 15:49:58 GMT

Hi Alessandro,

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

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

Re: Create Key with Code and YearMonth

alexandros17 — Fri, 02 Mar 2018 13:16:07 GMT

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

Re: Create Key with Code and YearMonth

jaumecf23 — Fri, 02 Mar 2018 13:20:08 GMT

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;