Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
That's exactely what I intended to do.
Please check attached sample.
Ceil( Month(DATE_ORD) / 2 ) As date,
based on requirement we can change the condition
hi
upload an image
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;
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;
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;
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]
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
now, i'm having a meeting.
I'll test your solution as soon as possible