product bifurcation with bucket

[prma7799]

Dear All,

I have one table like below

LOAD [Product Code],

min([Posting Date]) as [Invioce Date]
FROM [..\.qvd]  (qvd)

group by [Product Code];

in that i want create some type of bucket means if invoce date >= 1.4.2013 then new product and invoce date < 1.4.2013 is

legacy product.

How to achieve it.

Thanks

[sunny_talwar]

Maybe this:

LOAD [Product Code],

min([Posting Date]) as [Invioce Date],

If(min([Posting Date])>= Num(MakeDate(2013, 4, 1)), 'New Product', 'Legacy Product') as Bucket
FROM [..\.qvd]  (qvd)

group by [Product Code];

[eduardo_sommer]

Hi,

Try this:

OriginalTable:

LOAD [Product Code],

min([Posting Date]) as [Invioce Date]
FROM [..\.qvd]  (qvd)

group by [Product Code];

NewTable:

Load *,

     if([Invoice Date] < makedate(2013,4,1), 'legacy product', 'new product') as [Product Age]

Resident OriginalTable;

Drop  table OriginalTable;

I thik preceding load will not work because of the group by.

Regards

Eduardo

[eduardo_sommer]

Yes, this suggestion is better than mine

Eduardo

[prma7799]

getting error

Filed not found [Posting Date]

[prma7799]

Same error occur

[eduardo_sommer]

Are you sure you have this [Posting Date] field in your qvd file?

Eduardo

[prma7799]

if([First Invioce Date] <= makedate(2013,4,1), 'legacy product', 'new product') as [Product Age]

Am getting my result with this

Thanks...

[sunny_talwar]

Hahaha I am glad you were able to figure it out, but how do you expect us to know the field names that you in your application? We saw Posting Date and used that to determine if it is New or Legacy.

[sunny_talwar]

Preceding load will also work Eduardo.