topic Re: Need Unique data in QlikView

Need Unique data

Thu, 01 Oct 2015 20:41:28 GMT

I have a table where I have code number ,location ,region ,zone ,amount and date field.

Code no repeats ,

I want to have sum of amount with unique code no in a table and that should be at max of date.

Eg:

If code no 1235 is coming two times in a table and have two dates and two amounts associated then I want

Code no 1235 only for once in a table with sum of both amount associated with it and the date associated should be the max date.

Please help

Thanks

sunny_talwar — Thu, 01 Oct 2015 20:50:24 GMT

May be this:

Right Join (TableName)

LOAD [code number],

Sum(amount) as New_amount,

Date(Max(Date)) as Date

Resident TableName

Group By [code number]

maxgro — Thu, 01 Oct 2015 20:50:32 GMT

in user interface (chart) you can do it with a straight table chart

dimension code no

expression sum(amount)

max(date)

Thu, 01 Oct 2015 21:05:19 GMT

I have written

Qlik:

Load code no,

Sum(amount) as amount,

Date(max(date)) as date

From

C:.....

Group by code no;

Test:

Load

Code no,

Amount,

Date,

Code no&date as codeanddatekey

Resident qlik,

Drop table qlik;

Left join(test)

My full table

But the code no which were earlier having multiple dates are no more associated with any date after this code.need max date to come for that code.

Thanks