Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV Community,
This is my first of many posts as this is my first time using QV. A bit of background, I have absolutely no experience in programming and code. I've used excel extensively except for VB but that is about it [:)]
Ok, so I decided to do all the online courses to help prep me for the future ahead. I'm on the Course - Build your first QlikView 9 Application lesson 3 and already encountered an issue. In the previous lesson we had to edit our data to include a Year and Month list from the customer date. I did exactly as they asked and when i include the items in my properties box the list box comes up EMPTY. It does not show me year or month as it does in the tutorial. I have double-checked everything and my script is identical. Any help would be much appreciated
Thanks,
Byron
This actually worked LOL 🙂 Guess the date format for my country in the set default values is different to that to what appears in the comma delimited data text file. Changing the set value to read
Set Dateformat = 'DD/MM/YYY'; instead of 'YYYY/MM/DD' gave me what I needed.
Thanks all,
Cheers,
Byron
Hello,
How does your dates look like? How are they in the tutorial? Hwo does the script you are creating look like? It may be because some lack of date formatting.
Regards.
hi
while loading the your month/year filed, do like this..
SET
DateFormat='MM/YYYY';
LOAD date#(Fieldname) as Fieldname from sourceFile;
Regards
A'run'
Hi Miguel,
Below is the script I'm using. I've also included the SET Items which appears as default in the edit window. Let me know if this is what you are looking for. BTW
SET ThousandSep=' ';
SET
DecimalSep='.';SET
SET MoneyDecimalSep=',';
SET
MoneyFormat='R # ##0,00;R-# ##0,00';SET
TimeFormat='hh:mm:ss TT';SET
DateFormat='YYYY/MM/DD';SET
TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';SET
MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SalesDetails:
LOAD
[Actual Delivery Date],CustKey
as [Customer Number],
DateKey,
[Discount Amount]
,
[Invoice Date],
Year
([Invoice Date]) as Year,
Month
([Invoice Date]) as Month,
FROM
(txt, utf8, embedded labels, delimiter is ',', msq);
DATA\SALESDATA.TXTAbove is the code that I am using that is pertinent to my problem. Does anything look wrong
Sorry about the terrible formatting.... It did not look like that when I was replying (Did clean it up)
Hi Bandariarun,
I did load that on the script and same problem, nothing appears in my year or months list boxes
paste your load script once..
regards
A'Run'
SET ThousandSep=' ';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='R # ##0,00;R-# ##0,00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='YYYY/MM/DD';
LOAD date#([Invoice Date]) as InvoiceDate from DATA\SALESDATA.TXT;
SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SalesDetails:
LOAD [Actual Delivery Date],
CustKey as [Customer Number],
DateKey,
[Discount Amount],
[Invoice Date],
Year([Invoice Date]) as Year,
Month([Invoice Date]) as Month,
[Invoice Number],
[Item Class],
[Item Number],
[Line Desc 1] as Item,
[Line Number],
[List Price],
[Order Number],
[Promised Delivery Date],
[Sales Amount],
[Sales Amount Based on List Price],
[Sales Cost Amount],
[Sales Margin Amount],
[Sales Price],
[Sales Quantity],
[Sales Rep],
[U/M]
FROM
DATA\SALESDATA.TXT
(txt, utf8, embedded labels, delimiter is ',', msq);
Customer:
LOAD [Address Number],
[Business Family],
[Business Unit],
Customer,
[Customer Number],
[Customer Type],
Division,
[Line of Business],
Phone,
[Region Code],
[Regional Sales Mgr],
[Search Type]
FROM
DATA\CUSTOMERS.TXT
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
Where exists ([Customer Number])
;
Division:
LOAD Division,
[Division Name]
FROM
DATA\DIVISION.TXT
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Regions:
LOAD [Region Code],
[Region Name]
FROM
DATA\REGION.TXT
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
hi,
actully, here the content of [Invoice Date] field is loading as text format, not in date format. a simple solution is there.. update like this..
Subfield([Invoice Date],'/',1) as Year,
Subfield([Invoice Date],'/',2) as Month,
regards
A'run'
BTW, I need to read the forum rules on how to post et cetera. I see a lot of things like verify answer, suggested et cetera that I am unfamilar with as to what to do there 🙂 Will click verify though once it works.
Ok, so thanks for your reply, it did actually give me something in my list box, but not what I am looking for. In the month list box I have numbers 1 - 30 (only 12 months in a year) and the year box is 1-12, i want to see month (name) and year (actual year). Not sure what is happening here. Also, how could you tell that the field is loading as text format. I just checked the data now and I see that the format of the date is different to mine i.e. Data = 1/1/2000 where my date format is set to YYYY/MM/DD. Let me change the set format for date and see what happens