Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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)
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
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.
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
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.