topic Re: Double As in QlikView

Double As

Chanty4u — Mon, 13 Jun 2016 06:49:37 GMT

Hi all,

When i load below script it is givng error as Filed not found <Date>

how to over come dis?

Sample:

LOAD F1 as [s.no],

F2 as [Sales Executive],

F3 as [TopLine Achieved],

F4 as [Gross Margin Achieved] ,

F5 as [Top Line Target] ,

F6 as GMTarget,

F7 as Product,

F8 as BranchHead,

F9 as Branch,

F10 as Dept ,

F11 as Date

// Year(Date) as Year,

// Month(Date) as Month,

// Day(Date) as Day,

// Week(Date) as Week,

// 'Q' & ceil(Num(Month(Date))/3) as Quater,

// applyMap('FiscalQuarterMap', num(month(Date)),null()) AS FiscalQuarter

from[...];

Re: Double As

MindaugasBacius — Mon, 13 Jun 2016 06:52:47 GMT

Could you upload the sample?

Re: Double As

maleksafa — Mon, 13 Jun 2016 06:54:44 GMT

use the initial field name, which is F11

Re: Double As

marcus_sommer — Mon, 13 Jun 2016 06:54:51 GMT

You couldn't access the field Date within the load in which it will be generated - you need to use F11 instead, also:

year(F11) as Year

- Marcus

Re: Double As

Chanty4u — Mon, 13 Jun 2016 06:55:41 GMT

hi,

Sample qvw

Re: Double As

PrashantSangle — Mon, 13 Jun 2016 06:56:37 GMT

Hi,

mention error not belong to above script.

Are you doing any resident load or using newly created field Date in same load???

Please post complete script.

Regards

Re: Double As

Anonymous — Mon, 13 Jun 2016 06:57:11 GMT

Hi Chanty,

Sample:

LOAD F1 as [s.no],

F2 as [Sales Executive],

F3 as [TopLine Achieved],

F4 as [Gross Margin Achieved] ,

F5 as [Top Line Target] ,

F6 as GMTarget,

F7 as Product,

F8 as BranchHead,

F9 as Branch,

F10 as Dept ,

F11 as Date

Year(F11) as Year,

Month(F11) as Month,

Day(F11) as Day,

Week(F11) as Week,

'Q' & ceil(Num(Month(F11))/3) as Quater,

applyMap('FiscalQuarterMap', num(month(Date)),null()) AS FiscalQuarter

from[...];

Regards

Re: Double As

Kushal_Chawda — Mon, 13 Jun 2016 06:58:11 GMT

try this

Sample:

LOAD *,

Year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day,

Week(Date) as Week,

'Q' & ceil(Num(Month(Date))/3) as Quater,

applyMap('FiscalQuarterMap', num(month(Date)),null()) AS FiscalQuarter;

LOAD F1 as [s.no],

F2 as [Sales Executive],

F3 as [TopLine Achieved],

F4 as [Gross Margin Achieved] ,

F5 as [Top Line Target] ,

F6 as GMTarget,

F7 as Product,

F8 as BranchHead,

F9 as Branch,

F10 as Dept ,

F11 as Date

from[...];

Re: Double As

Chanty4u — Mon, 13 Jun 2016 06:58:19 GMT

if i use dat ... its working but it is not givng the values in field

Re: Double As

Chanty4u — Mon, 13 Jun 2016 06:59:15 GMT

i tried that but... its wrkng fyn... but the year and month values are not shwng. its gvng empty field

Re: Double As

PrashantSangle — Mon, 13 Jun 2016 06:59:21 GMT

Hi,

Yes, That could be possible error if those field not commented in his original post.

Regards,

Re: Double As

Chanty4u — Mon, 13 Jun 2016 07:01:12 GMT

same kush.... its nt givng the values in the field

Re: Double As

PrashantSangle — Mon, 13 Jun 2016 07:05:49 GMT

Hi,

try with date#()

month(date#(dateField,'DD-MMM-yy')) as Month

Regards

Re: Double As

Kushal_Chawda — Mon, 13 Jun 2016 07:06:33 GMT

Your dates are in string format use Date#

Sample:

LOAD *,

Year(date#(Date,'DD-MMM-YY')) as Year,

Month(date#(Date,'DD-MMM-YY')) as Month,

Day(date#(Date,'DD-MMM-YY')) as Day,

Week(date#(Date,'DD-MMM-YY')) as Week,

'Q' & ceil(Num(Month(date#(Date,'DD-MMM-YY')))/3) as Quater,

applyMap('FiscalQuarterMap', num(month(Date)),null()) AS FiscalQuarter;

LOAD F1 as [s.no],

F2 as [Sales Executive],

F3 as [TopLine Achieved],

F4 as [Gross Margin Achieved] ,

F5 as [Top Line Target] ,

F6 as GMTarget,

F7 as Product,

F8 as BranchHead,

F9 as Branch,

F10 as Dept ,

F11 as Date

from[...];

Re: Double As

Anonymous — Mon, 13 Jun 2016 07:06:39 GMT

Hi Chanty,

This is because your format on your original table is not on Date format. Could you share one example of your data?

Thanks

Re: Double As

Anonymous — Mon, 13 Jun 2016 07:10:19 GMT

Hi Chanty,

Try kushal chawda solution. On Applymap apply the Date# expression too.

Regards!

Re: Double As

Chanty4u — Mon, 13 Jun 2016 07:12:31 GMT

yes its givng correct now...but Quarter value shwng as only "Q"

instead of Q1 and Q2 Q3 Q4...?

Re: Double As

Chanty4u — Mon, 13 Jun 2016 07:15:06 GMT

evrythng is wrkng except fiscal quarter

Re: Double As

PrashantSangle — Mon, 13 Jun 2016 07:16:58 GMT

Hi,

try

'Q' & ceil(Month(date#(Date,'DD-MMM-YY'))/3) as Quater

Regards

Re: Double As

tamilarasu — Mon, 13 Jun 2016 07:18:34 GMT

Should be this,

applyMap('FiscalQuarterMap', num(month(Date#(Date,'DD-MMM-YYYY'))),null()) AS FiscalQuarter;