Year/Month in Edit Script Not Working

[#1]

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

[Miguel_Angel_Baeyens]

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.

[#3]

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'

[#4]

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

[#5]

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

[#6]

Hi Bandariarun,

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

[#7]

paste your load script once..

regards

A'Run'

[#8]

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

[#9]

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'

[#10]

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