topic Re: Month and Year from Date Field in QlikView

Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:15:10 GMT

Hi All,

I need some help. I have been trying to extract the Year and Month to match our Financial Year and Period with the script below.

In SQL it works but in Qlikview it seems to run once, instead of for each line.

I need to make the Financial Year start from 1st July of each year being equal to the first period(month).

Edited: From the responses, i have added a PeriodYear field and added as a list box, but I would still need to somehow automate it to be selected. eg. if i select Year 2015 and Period 5 (Being November 2014), then it should link with 052015 in the results.

SELECT T1.NUM_0,T1.SIDLIN_0,T2.ACCDAT_0,MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY,

"Period"&"FIY" as PerYr

FROM SINVOICED T1, SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

Re: Month and Year from Date Field

vinay_hg — Mon, 03 Nov 2014 08:21:06 GMT

hi, give 2 input box to capture period, put this 2 values in variables then compare the date between the given values in varibles in expression or if u take dimention as date with dimention.(claculated dimension). by this u can provide user to select the required dynamic periods for selection.

Re: Month and Year from Date Field

ashfaq_haseeb — Mon, 03 Nov 2014 08:24:27 GMT

Hi,

try this

MonthName(Yourdatefield) as MonthYear.

let me know if that worked.

Regards

ASHFAQ

Re: Month and Year from Date Field

its_anandrjs — Mon, 03 Nov 2014 08:31:50 GMT

Hi,

After getting table into the Qlikview you can concatenate this two fields

Load

[Month] & [Year] as [Month Year]

From Location;

And if format problem then convert to its appropriate date format

Regards

Anand

Re: Month and Year from Date Field

anbu1984 — Mon, 03 Nov 2014 08:36:33 GMT

Can you post QV script you tried

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:37:48 GMT

Hi Vinay, can you give more detail or modify my SQL query to show what you are saying.

On the Sales Detail screenshot, I am expecting to select only Year and Period and require all the Sales Detail for that period. I am not able to add more selections, as it would affect other Tabs.

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:38:39 GMT

View below

InvoiceLines:

LOAD

"CPY_0" AS [Company],

"NUM_0" as SalesInvNum,

"SIDLIN_0" as InvLine,

if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("NETPRI_0"*"QTY_0")*-1, "NETPRI_0"*"QTY_0")) as Sell,

if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("CPRPRI_0"*"QTY_0")*-1, "CPRPRI_0"*"QTY_0")) as Cost,

"GROPRI_0",

"NETPRI_0",

"CPRPRI_0",

"ITMREF_0" as Product,

"ITMDES1_0" as ProdDesc,

round("QTY_0",0.01) as QTY_0,

round("PFM_0",0.01) as PFM_0,

0 as Forecast,

round(applymap('SOQTY',"SOHNUM_0"&"SOPLIN_0", QTY_0),0.01) as OrderedQty,

0 as Freight,

"ACCDAT_0" as InvAccDate,

"Month" as InvMonth,

"Year" as InvYear,

"Period",

"FIY"

;

SQL

SELECT T1.*, T2.INVTYP_0, T2.ACCDAT_0, MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY

FROM x3live.GBILIVE.SINVOICED T1, x3live.GBILIVE.SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

Re: Month and Year from Date Field

anbu1984 — Mon, 03 Nov 2014 08:50:16 GMT

"In SQL it works but in Qlikview it seems to run once, instead of for each line." -- Can you explain your issue with example and expected output

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:52:16 GMT

Hi Ashfaq,

It crashed my qvw with the following error and Out of Object Memory after 5 mins of being frozen.

"One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog."

Re: Month and Year from Date Field

jagan — Mon, 03 Nov 2014 08:53:03 GMT

Hi,

You need to remove Double quotes for the below two lines

"Month" as InvMonth,

"Year" as InvYear,

Try this script

InvoiceLines:

LOAD

"CPY_0" AS [Company],

"NUM_0" as SalesInvNum,

"SIDLIN_0" as InvLine,

if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("NETPRI_0"*"QTY_0")*-1, "NETPRI_0"*"QTY_0")) as Sell,

if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("CPRPRI_0"*"QTY_0")*-1, "CPRPRI_0"*"QTY_0")) as Cost,

"GROPRI_0",

"NETPRI_0",

"CPRPRI_0",

"ITMREF_0" as Product,

"ITMDES1_0" as ProdDesc,

round("QTY_0",0.01) as QTY_0,

round("PFM_0",0.01) as PFM_0,

0 as Forecast,

round(applymap('SOQTY',"SOHNUM_0"&"SOPLIN_0", QTY_0),0.01) as OrderedQty,

0 as Freight,

"ACCDAT_0" as InvAccDate,

[Month] as InvMonth,

[Year] as InvYear,

"Period",

"FIY"

;

SQL

SELECT T1.*, T2.INVTYP_0, T2.ACCDAT_0, MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY

FROM x3live.GBILIVE.SINVOICED T1, x3live.GBILIVE.SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

Hope this helps you.

REgards,

Jagan.

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:54:52 GMT

Hi Anand,

It crashed my qvw with the following error and Out of Object Memory after 5 mins of being frozen.

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:56:15 GMT

Hi Anbu,

The Same Query in SQL and Qv are having different results, basically.

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 08:57:04 GMT

Hi Jagan,

Those are my checks. I will remove the fields once I have confirmed that the Period and FIY are correct.

Re: Month and Year from Date Field

its_anandrjs — Mon, 03 Nov 2014 09:01:05 GMT

Hi,

From the Qvd file you have to do this not directly to the SQL query because it created problem while running may be due to field references.

Regards

Anand

Re: Month and Year from Date Field

djsampat — Mon, 03 Nov 2014 09:10:12 GMT

Hi Anand,

Not sure I understand. Can you explain how to fix it?

Re: Month and Year from Date Field

djsampat — Tue, 04 Nov 2014 02:24:42 GMT

Resolved:

InvoiceLines:

LOAD

"CPY_0" AS [Company],

"SALFCY_0" as [Site],

"NUM_0" as SalesInvNum,

"SIDLIN_0" as InvLine,

if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("NETPRI_0"*"QTY_0")*-1, "NETPRI_0"*"QTY_0")) as Sell,

if("QTY_0" = 0, IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, "AMTLIN_0"*-1,"AMTLIN_0"),IF("INVTYP_0" = 2 OR "INVTYP_0" = 4, ("CPRPRI_0"*"QTY_0")*-1, "CPRPRI_0"*"QTY_0")) as Cost,

"GROPRI_0",

"NETPRI_0",

"CPRPRI_0",

"ITMREF_0" as Product,

"ITMDES1_0" as ProdDesc,

round("QTY_0",0.01) as QTY_0,

round("PFM_0",0.01) as PFM_0,

0 as Forecast,

round(applymap('SOQTY',"SOHNUM_0"&"SOPLIN_0", QTY_0),0.01) as OrderedQty,

0 as Freight,

date("ACCDAT_0", 'DD/MM/YYYY') as AccDate,

"Month" as InvMonth,

"Year" as InvYear,

"Period",

"FIY" as Year,

"Period"&"FIY" as PerYr

;

SQL

SELECT T1.*, T2.INVTYP_0, T2.ACCDAT_0, MONTH(T2.ACCDAT_0) as [Month], YEAR(T2.ACCDAT_0) as [Year],

case when MONTH(T2.ACCDAT_0) > 6 then MONTH(T2.ACCDAT_0)-6 else MONTH(T2.ACCDAT_0)+6 end as Period,

case when MONTH(T2.ACCDAT_0) > 6 then YEAR(T2.ACCDAT_0)+1 else YEAR(T2.ACCDAT_0) end as FIY

FROM x3live.GBILIVE.SINVOICED T1, x3live.GBILIVE.SINVOICE T2

WHERE T1.NUM_0 = T2.NUM_0

;