topic Year/Month in Edit Script Not Working in QlikView

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 12:01:13 GMT

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

Year/Month in Edit Script Not Working

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 12:18:09 GMT

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.

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 12:22:21 GMT

while loading the your month/year filed, do like this..

SET

DateFormat='MM/YYYY';

LOAD date#(Fieldname) as Fieldname from sourceFile;

Regards

A'run'

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 12:30:19 GMT

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';

MoneyThousandSep=' ';

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

Above is the code that I am using that is pertinent to my problem. Does anything look wrong

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 12:31:24 GMT

Sorry about the terrible formatting.... It did not look like that when I was replying (Did clean it up)

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 12:37:19 GMT

Hi Bandariarun,

I did load that on the script and same problem, nothing appears in my year or months list boxes

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 12:44:03 GMT

paste your load script once..

regards

A'Run'

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 13:02:42 GMT

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);

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 13:16:58 GMT

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'

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 13:30:53 GMT

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

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 13:31:05 GMT

Hi,

My suggetion would be for you to use Date#() function.

Take for example the date format in [Invoice Date] field is like 'YYYY-MM-DD'. you script for creating Year, Month would be as follow.

Date#( [Invoice Date] ,'YYYY-MM-DD') as Invoice_Date,

Year(Date#( [Invoice Date] ,'YYYY-MM-DD') ) as Year,

Month(Date#( [Invoice Date] ,'YYYY-MM-DD') ) as Month

Hope this will help you.

- Sridhar

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 13:35:18 GMT

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

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 13:51:27 GMT

@ SE, thanks for your post, but that didn't work; Think I've come a bit closer now with the formatting though. I have however now run into another rather annoying problem. The line chart I have with the year as the Y axis will not sort in order. No matter what I do by sort by y-axis ascending, descending et cetera does not work. I also have a '-' appearing on the axis that looks like an accumulation of the 3 years amounts for some reason.

Hmmmm I think my date format is somehow affecting this. Could I ask for you guys to please download the data for that tutorial, and have at look at the SALESDATE.txt file and let me know if there is something in there that can be causing this.

I have also made a comment about the tutorial and asked them to review these tutorials as certain aspects are not generic for all countries. Thanks though to everyone for your assistance so far, much appreciated

Is there anyway I can attach my QVW file so that you guys can view for yourself?

Byron

Year/Month in Edit Script Not Working

Tue, 15 Mar 2011 15:33:45 GMT

Ya, will look into that salesman.txt.

You couold attach your QV document when you post / reply for the thread.

Go to Reply>>options from top menu>> ADD/UPDATE (File Attachment)>> browse your doc and say upload.

- Sridhar