topic Re: YEAR / months in QlikView

YEAR / months

fmazzarelli — Wed, 01 Jul 2015 11:13:08 GMT

Hi,

i would create a variable dividing the year in 2 - 3 - 4 - 5 months.

a field "data" is colled DATE_ORD - YYYY/MM/GG

How could I do?

2 months:

gen - feb

mar - apr

mag - giu

lug - ago

set - ott

nov - dic

3 months:

gen - feb - mar

apr - mag - giu

lug - ago -set

ott - nov - dic

4 month

gen - feb - mar - apr

mag - giu lug - ago

set - ott - nov - dic

6 months
gen - feb - mar - apr - mag - giu

lug - ago - set - ott - nov - dic

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 11:20:41 GMT

Something like

LOAD *,

Ceil( Month(DATE_ORD) / 2 ) As TwoMonthPeriod,

Ceil( Month(DATE_ORD) / 3 ) As ThreeMonthPeriod,

Ceil( Month(DATE_ORD) / 4 ) As FourMonthPeriod,

Ceil( Month(DATE_ORD) / 6 ) As SixMonthPeriod;

LOAD Date#(DATE_ORD,'YYYY/MM/DD') as DATE_ORD

FROM YourTableSource;

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 11:32:50 GMT

looking your code should be fine

I'm using it during reload data function. Is it correct?

Because "the command Ceil is a value unrecognized.

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 11:35:19 GMT

ah, i'm using an sql table

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 11:36:42 GMT

Is that the error you get when reloading?

ceil() is a numeric function and should work in a QV LOAD statement. Could you post your script or document log?

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 11:37:32 GMT

select

campo1

,campo2

,campo3

from nomedatabase.[dbo].[nometabella]

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 11:37:50 GMT

You need to use the QV functions in a preceding LOAD statement:

LOAD *,

Ceil( Month(DATE_ORD) / 2 ) As TwoMonthPeriod,

Ceil( Month(DATE_ORD) / 3 ) As ThreeMonthPeriod,

Ceil( Month(DATE_ORD) / 4 ) As FourMonthPeriod,

Ceil( Month(DATE_ORD) / 6 ) As SixMonthPeriod;

LOAD Date#(DATE_ORD,'YYYY/MM/DD') as DATE_ORD;

SQL SELECT ....

FROM YourTableSource;

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 11:52:03 GMT

hi,

but the field DATE_ORD is extracted by a select (sql).

I tried to put them after but, i receive a syntax error (FROM is missing

SQL SELECT

[DATE_ORD]

FROM [DATABASE].[dbo].

;

LOAD *,

Ceil( Month(DATE_ORD) / 2 ) As TwoMonthPeriod,

Ceil( Month(DATE_ORD) / 3 ) As ThreeMonthPeriod,

Ceil( Month(DATE_ORD) / 4 ) As FourMonthPeriod,

Ceil( Month(DATE_ORD) / 6 ) As SixMonthPeriod;

LOAD Date#(DATE_ORD,'YYYY/MM/DD') as DATE_ORD;

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 11:55:57 GMT

That's why a called it a preceding LOAD statement:

LOAD *,

Ceil( Month(DATE_ORD) / 2 ) As TwoMonthPeriod,

Ceil( Month(DATE_ORD) / 3 ) As ThreeMonthPeriod,

Ceil( Month(DATE_ORD) / 4 ) As FourMonthPeriod,

Ceil( Month(DATE_ORD) / 6 ) As SixMonthPeriod;

LOAD Date#(DATE_ORD,'YYYY/MM/DD') as DATE_ORD;

SQL SELECT

[DATE_ORD]

FROM [DATABASE].[dbo].

;

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 12:08:18 GMT

It works

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 13:21:54 GMT

i afraid but probably my request was bad presented.

i need to Group the months

Example

gen + feb as a criteria of selection of all items included in gen + feb and so on

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 13:38:34 GMT

That's exactely what I intended to do.

Please check attached sample.

Re: YEAR / months

pratap6699 — Wed, 01 Jul 2015 15:38:00 GMT

Ceil( Month(DATE_ORD) / 2 ) As date,

based on requirement we can change the condition

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 20:04:50 GMT

upload an image

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 20:15:01 GMT

You are not using Ceil() function in your Bimestre field generation, right?

If you want to map the number 1,2,3,4,5,6 to Jan-Feb, Mar-Apr,... you can do it like this:

TwoMonthMap:

MAPPING LOAD *;

LOAD F1, dual(F2,F1) as F3 INLINE [

F1,F2

1, Jan-Feb

2, Mar-Apr

3, May-Jun

4, Jul-Aug

5, Sep-Oct

6, Nov-Dec

];

LOAD *, ApplyMap('TwoMonthMap', TwoMonthPeriod) as TwoMonthPeriodName;

LOAD *,

Ceil( Month(DATE_ORD) / 2 ) As TwoMonthPeriod,

Ceil( Month(DATE_ORD) / 3 ) As ThreeMonthPeriod,

Ceil( Month(DATE_ORD) / 4 ) As FourMonthPeriod,

Ceil( Month(DATE_ORD) / 6 ) As SixMonthPeriod;

LOAD *, Month(DATE_ORD) as Month;

LOAD Date(makedate(2014)+recno()-1,'YYYY-MM-DD') AS DATE_ORD

AUTOGENERATE 365;

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 20:41:57 GMT

i'm watching a video about qlik.... but I'm using ceil function.

Meanwhile, I attach my script.

my script is

LOAD *,

Ceil(Month(FATT_DAT))/2 AS BIMESTRE,

Ceil(Month(FATT_DAT))/3 AS TRIMESTRE,

Ceil(Month(FATT_DAT))/4 AS QUADRIMESTRE,

Ceil(Month(FATT_DAT))/6 AS SEMESTRE;

SQL Select [ANAB_RAG_SOC]

,[FATT_KY]

,[FATT_DAT]

,[FATT_TIPKY]

,[FATT_CLIENTEKY]

,[FATT_PAR_IVA]

,[FATT_COD_FIS]

,[FATT_VALUTAKY]

,[FATT_CAMBIO]

,[FATT_COD_PAGKY]

,[FATT_COD_BANKY]

,[FATT_NOTE]

,[FATT_NOTE_STAMPA]

,[FATT_NUMERO]

,[FATT_APPKY]

,[FATT_ID_APPKY]

,[FATT_IND_ANA_INDKY]

,[FATT_STAMPA]

,[FATT_TRAVASO]

,[FATT_DICH_INT]

,[FATT_PROVA]

,[FATT_TAR_CLIKY]

,[FATT_TOT_IVA]

,[FATT_TOT]

,[FATT_TOT_IMPONIBILE]

,[FATT_SERIE]

,[FATT_FATT_ID_SOCIOKY]

,[FATT_NOTE_STAMPA_TIPO]

,[FATT_NC_FATT_STORKY]

,[FATT_SOC_CLIENTEKY]

,[FATT_SOC_COD_PAGKY]

,[FATT_SOC_COD_BANKY]

,[FATT_SOC_SOC]

,[FATT_SOC]

,[FATT_NUMERO_EXT]

,[FATT_COMP_ANNO]

,[FATT_COMP_MESE]

,[FATT_TIP_GRP]

,[FATT_TIP_GRP_IDENT]

,[FATT_BOLLO_VIRTUALE]

,[FATT_GRFKY]

,[FATT_IBAN]

,[FATT_BIC]

, year(FATT_DAT) as Anno

, month(FATT_DAT) as Mese

FROM [FreightEurocoop].[dbo].[FATTURE_TESTATA]

inner join ANABASE

ON ANAB_KY = FATT_CLIENTEKY;

Re: YEAR / months

swuehl — Wed, 01 Jul 2015 21:08:26 GMT

You need to enclose the division by 2 into the ceil() function call.

BimestreMap:

MAPPING LOAD *;

LOAD F1, dual(F2,F1) as F3 INLINE [

F1,F2

1, Jan-Feb

2, Mar-Apr

3, May-Jun

4, Jul-Aug

5, Sep-Oct

6, Nov-Dec

];

Table:

LOAD *, Applymap('BimestreMap', BIMESTRE) as BIMESTRE_TEXT;

LOAD *,

Ceil(Month(FATT_DAT) /2) AS BIMESTRE,

Ceil(Month(FATT_DAT) /3) AS TRIMESTRE,

Ceil(Month(FATT_DAT) /4) AS QUADRIMESTRE,

Ceil(Month(FATT_DAT) /6) AS SEMESTRE;

SQL Select [ANAB_RAG_SOC]

,[FATT_KY]

,[FATT_DAT]

,[FATT_TIPKY]

,[FATT_CLIENTEKY]

,[FATT_PAR_IVA]

,[FATT_COD_FIS]

,[FATT_VALUTAKY]

,[FATT_CAMBIO]

,[FATT_COD_PAGKY]

,[FATT_COD_BANKY]

,[FATT_NOTE]

,[FATT_NOTE_STAMPA]

,[FATT_NUMERO]

,[FATT_APPKY]

,[FATT_ID_APPKY]

,[FATT_IND_ANA_INDKY]

,[FATT_STAMPA]

,[FATT_TRAVASO]

,[FATT_DICH_INT]

,[FATT_PROVA]

,[FATT_TAR_CLIKY]

,[FATT_TOT_IVA]

,[FATT_TOT]

,[FATT_TOT_IMPONIBILE]

,[FATT_SERIE]

,[FATT_FATT_ID_SOCIOKY]

,[FATT_NOTE_STAMPA_TIPO]

,[FATT_NC_FATT_STORKY]

,[FATT_SOC_CLIENTEKY]

,[FATT_SOC_COD_PAGKY]

,[FATT_SOC_COD_BANKY]

,[FATT_SOC_SOC]

,[FATT_SOC]

,[FATT_NUMERO_EXT]

,[FATT_COMP_ANNO]

,[FATT_COMP_MESE]

,[FATT_TIP_GRP]

,[FATT_TIP_GRP_IDENT]

,[FATT_BOLLO_VIRTUALE]

,[FATT_GRFKY]

,[FATT_IBAN]

,[FATT_BIC]

, year(FATT_DAT) as Anno

, month(FATT_DAT) as Mese

FROM [FreightEurocoop].[dbo].[FATTURE_TESTATA]

inner join ANABASE

ON ANAB_KY = FATT_CLIENTEKY;

Re: YEAR / months

fmazzarelli — Wed, 01 Jul 2015 21:11:31 GMT

sorry, I corrected my script as below

and it seems work fine.

Only a doubt as when I select the period Jan-Feb, the month FEB is selected.

I have to check the values

Script is:

TwoMonthMap:

MAPPING LOAD *;

LOAD F1, dual(F2,F1) as F3 INLINE [

F1,F2

1, Jan-Feb

2, Mar-Apr

3, May-Jun

4, Jul-Aug

5, Sep-Oct

6, Nov-Dec

];

LOAD *, ApplyMap('TwoMonthMap', BIMESTRE) as TwoMonthPeriodName;

LOAD *,

Ceil(Month(FATT_DAT))/2 AS BIMESTRE;

// Ceil(Month(FATT_DAT))/3 AS TRIMESTRE,

// Ceil(Month(FATT_DAT))/4 AS QUADRIMESTRE,

// Ceil(Month(FATT_DAT))/6 AS SEMESTRE;

LOAD *, Month(FATT_DAT) as Month;

LOAD Date(makedate(2015)+recno()-1,'YYYY-MM-DD') AS FATT_DAT

AUTOGENERATE 365;

SQL Select [ANAB_RAG_SOC]

,[FATT_KY]

,[FATT_DAT]

Re: YEAR / months

MarcoWedel — Thu, 02 Jul 2015 00:00:41 GMT

Hi,

one solution could be also:

SET MonthNames='Gen;Feb;Mar;Apr;Mag;Giu;Lug;Ago;Set;Ott;Nov;Dic';

tabMesi:
LOAD *,
    Month(FATT_DAT) as Mese,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),2,1)*4-3, 7),';',' - '),Floor(Month(FATT_DAT),2,1)) as BIMESTRE,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),3,1)*4-3,11),';',' - '),Floor(Month(FATT_DAT),3,1)) as TRIMESTRE,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),4,1)*4-3,15),';',' - '),Floor(Month(FATT_DAT),4,1)) as QUADRIMESTRE,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),6,1)*4-3,23),';',' - '),Floor(Month(FATT_DAT),6,1)) as SEMESTRE;
// Test data generation
LOAD Date(MakeDate(2015)+RecNo()-1,'YYYY/MM/DD') as FATT_DAT
AutoGenerate 365;

hope this helps

regards

Marco

Re: YEAR / months

fmazzarelli — Thu, 02 Jul 2015 12:40:54 GMT

now, i'm having a meeting.

I'll test your solution as soon as possible