Month and Year from Date Field

[djsampat]

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

[djsampat]

Hi Anand,

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."

[djsampat]

Hi Anbu,

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

[djsampat]

Hi Jagan,

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

[its_anandrjs]

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

[djsampat]

Hi Anand,

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

[djsampat · Accepted solution]

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

;