Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Qlikview Load Date Issues

I'm wondering if someone could help me with an issue I've been having

I have been asked to provide a report showing monthly transactional data which automatically updates to only show the current month.

I have tried using various today() type commands in the load script but none of them seem to work

I am trying to change the highlighted transaction date in the code below to the first of the current month

What command should I be using?

UNDERWRITERS:

LOAD RIGHT(LEFT("CLIENT_ACCOUNT_CODE_AND_NAME",7),6) & ' - ' & RIGHT("CLIENT_ACCOUNT_CODE_AND_NAME",LEN("CLIENT_ACCOUNT_CODE_AND_NAME")-7) AS "Client Account",

    "ASSURED_CODE" & ' - ' & "INSURED_NAME" AS "Insured Account",

    "INSURER_CODE" & ' - ' & "INSURER_NAME" AS "Insurer Account",

    IF(Left("CLIENT_ACCOUNT_CODE_AND_NAME",2)='CA','Agent','Direct') AS ClientType,

    IF("DEPARTMENT_CODE"='O' Or "DEPARTMENT_CODE"='T' Or "DEPARTMENT_CODE"='V','FR','IB') AS Business,

    "DEPARTMENT_NAME",

    "SETTLEMENT_BROKERAGE" AS SettBrokerage,

    "SETTLEMENT_CURRENCY" AS CCY_Code,

    "SETTLEMENT_GROSS_PREMIUM" AS SettGross,

    "SETTLEMENT_NET_PREMIUM" AS SettNet,

    "TRANSACTION_DATE",

    "LEDGER_POSTED_DATE",

    IF("CLIENT_CPI_SCORE"<'6',"CLIENT_CPI_SCORE",Null()) AS ClientCPI,

    IF("ASSURED_CPI_SCORE"<'6',"ASSURED_CPI_SCORE",Null()) AS InsuredCPI,

    IF("UWTR_CPI_SCORE"<'6',"UWTR_CPI_SCORE",Null()) AS InsurerCPI,

    "CLIENT_COUNTRY_OF_ORIGIN" AS ClientCountryCode,

    "ASSURED_COUNTRY_OF_ORIGIN" AS InsuredCountryCode,

    "UWTR_COUNTRY_OF_ORIGIN" AS InsurerCountryCode,

    "TRANSACTION_REFERENCE";

SQL SELECT "CLIENT_ACCOUNT_CODE_AND_NAME",

    "DEPARTMENT_CODE",

    "DEPARTMENT_NAME",

    "INSURED_NAME",

    "INSURER_CODE",

    "INSURER_NAME",

    "LEDGER_POSTED_DATE",

    "SETTLEMENT_BROKERAGE",

    "SETTLEMENT_CURRENCY",

    "SETTLEMENT_GROSS_PREMIUM",

    "SETTLEMENT_NET_PREMIUM",

    "TRANSACTION_DATE",

    "UNDERWRITER_ACCOUNT_CODE",

    "CPI_SCORE",

    "CLIENT_STATUS_CODE",

    "ASSURED_CODE",

    "CLIENT_CPI_SCORE",

    "ASSURED_CPI_SCORE",

    "UWTR_CPI_SCORE",

    "CLIENT_COUNTRY_OF_ORIGIN",

    "ASSURED_COUNTRY_OF_ORIGIN",

    "UWTR_COUNTRY_OF_ORIGIN",

    "TRANSACTION_REFERENCE"

FROM UNDERWRITERS where ("TRANSACTION_DATE" >= '01-05-2011') AND ("CLIENT_CPI_SCORE"<'6' Or "ASSURED_CPI_SCORE"<'6' Or "UWTR_CPI_SCORE"<'6');

Thnks in advance for your help

1 Solution

Accepted Solutions
Not applicable

Re: Qlikview Load Date Issues

I eventually found out how to do what I was proposing with the help of these forums and various colleagues. I needed to create a variable with the generated date before I could reference it

First I created the new variable at the beginning of the load script

Let vStartDate = Date(AddYears(YearStart(Today(),0,5),-2),'YYYY-MM-DD');

I then put the following line of code into the WHERE part of my SQL statement

("TRANSACTION_DATE">='$(vStartDate)')

Thanks to everyone that offered a solution.

5 Replies
ToniKautto
Not applicable

Re: Qlikview Load Date Issues

Usually you would load your data first, and then make the time limitation within the actual objects that show your data. This way you will create a solid data model and add dynamic possibility to alter the presentation.

Please describe in more details exactly what problem you are experiencing, as I can not find any highlighted datat in the above entry.

---

Note that you can find more details on date and time funcitons in the Reference Manual (http://download.qlik.com) or in the Desktop Client help (Press F1)

Not applicable

Re: Qlikview Load Date Issues

My apologies, the date at the bottom was highlighted in the edit screen but not on the posted massage

This is the part of the code that was supposed to be highlighted

FROM UNDERWRITERS where ("TRANSACTION_DATE" >= '01-05-2011')

I want to be able to replace the date with a variable showing the first of the current month

I have gotten round the issue on some of our smaller reports by doing exactly as you suggest but due to the large amount of data in the database I want to be able to pull through the bare minimum number of lines to improve reload times

Thanks

ToniKautto
Not applicable

Re: Qlikview Load Date Issues

Since you are running SQL that parts is executed on the SQL server, so a bit hard to give an exact answer but best guess is that the format is not recognised by the server. You could check what format is used on the database server and try to apply the same in your SQL syntax.

Regarding the today() call SQL in general uses GETDATE() for today's date, maybe that works for your server as well.

Still I would try to load the entire tale to give better possibility to present data in your QlikView application. By not limiting the data initially you will get grounds to do more analysis, like comparing current month with previous months or even compare Year Over Year. This is really what the basic ideas behind QlikView is all about. Of course if you load massive amounts of data you might want to limit the load to keep the QVW small, but else than that I would recommend that you load without limitations.

Not applicable

Qlikview Load Date Issues

Hi.

If i understand you, you want something like this :

first day of this month in QV script :  DATE(NOW(),'YYYY-MM-01')

same in t-sql : cast(convert(varchar(8), getdate(), 21) + '01' as datetime)

21 - it's time index, depend of what time format you use

if problems with format, there are many solutions, for example:

year(TRANSACTION_DATE) = year(getdate()) and month(TRANSACTION_DATE) = month(getdate())

sorry, but i use reload from csv files, and a don't know, what code you need - t-sql or qlikview, when do reload from server.

Hope this will help you.

Regards,

Igor

Not applicable

Re: Qlikview Load Date Issues

I eventually found out how to do what I was proposing with the help of these forums and various colleagues. I needed to create a variable with the generated date before I could reference it

First I created the new variable at the beginning of the load script

Let vStartDate = Date(AddYears(YearStart(Today(),0,5),-2),'YYYY-MM-DD');

I then put the following line of code into the WHERE part of my SQL statement

("TRANSACTION_DATE">='$(vStartDate)')

Thanks to everyone that offered a solution.