topic Re: Qlikview Load Date Issues in QlikView

Qlikview Load Date Issues

Fri, 06 May 2011 09:05:06 GMT

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

Re: Qlikview Load Date Issues

ToniKautto — Fri, 06 May 2011 09:13:52 GMT

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)

Re: Qlikview Load Date Issues

Fri, 06 May 2011 09:22:22 GMT

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

Re: Qlikview Load Date Issues

ToniKautto — Fri, 06 May 2011 09:41:56 GMT

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.

Qlikview Load Date Issues

Fri, 06 May 2011 10:18:26 GMT

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

Re: Qlikview Load Date Issues

Fri, 12 Aug 2011 16:26:48 GMT

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.