topic Re: Association is missing in QlikView

Association is missing

Anonymous — Wed, 16 Nov 2016 07:16:42 GMT

Hi,

I have yearmonth (combination of year n month), sales, salesperson data.

I have taken year and months from yearmonth filed.

I have created two listboxes as year and month a bar chart with sum of sales.

how ever if i wish to select year and month at a time the selection is not getting possible in the list boxes.

Ex: if i want to select 2012 Jan the selection is missing and association is not taking place,

Please let me know by seeing the attachment

Re: Association is missing

Anonymous — Wed, 16 Nov 2016 07:29:18 GMT

any help please

Re: Association is missing

Anonymous — Wed, 16 Nov 2016 07:32:30 GMT

Try this:

Use preceding load:

Association:

load YearMonth,

Right(YearMonth,4) as Year,

left(YearMonth,3) as Month,

Sales,

SalesPerson;

LOAD * INLINE [

YearMonth, Sales, SalesPerson

jan2012, 100, John

feb2012, 250, James

mar2012, 300, Tim

jan2013, 400, Tom

feb2013, 500, Hatson

mar2013, 600, Adsr

];

Then change your list boxes to use Year & Month fields respectively and Edit your group on the bar chart to use these fields as well...

Sample attached...

Hope this helps you!

Re: Association is missing

tamilarasu — Wed, 16 Nov 2016 07:35:48 GMT

Hi John,

Have a look at the attachment.

Association:
Load *, Date(Date#(Left(YearMonth,3),'MMM'),'MMM') as Month, Date#(Right(YearMonth,4),'YYYY') as Year;
LOAD * INLINE [
YearMonth, Sales, SalesPerson
jan2012, 100, John
feb2012, 250, James
mar2012, 300, Tim
jan2013, 400, Tom
feb2013, 500, Hatson
mar2013, 600, Adsr
];

Re: Association is missing

its_anandrjs — Wed, 16 Nov 2016 07:49:37 GMT

Create this Year and Month in your load script will be better.

Association:

Load

year(Date#(YearMonth,'MMMYYYY')) as Year,

Month(Date#(YearMonth,'MMMYYYY')) as Month;

LOAD * INLINE [

YearMonth, Sales, SalesPerson

jan2012, 100, John

feb2012, 250, James

mar2012, 300, Tim

jan2013, 400, Tom

feb2013, 500, Hatson

mar2013, 600, Adsr

];

See the attached file.

Regards

Anand

Re: Association is missing

Anonymous — Wed, 16 Nov 2016 07:53:17 GMT

Thanks all for your responses

I dont want to split the year and month fields at load script level,

I want to split them in application side only.

Re: Association is missing

jonathandienst — Wed, 16 Nov 2016 08:10:25 GMT

Best practice is to convert date fields (like your yearmonth) into proper numerical dates and if you are analysing by year and month, to create year and month fields. Using calculated fields in list boxes effectively makes selections on the underlying fields and this may not be quite what you want.

Load like this

Association:

LOAD *,

Year(YearMonth) as Year,

Month(YearMonth) as Month

;

LOAD Date#(YearMonth, 'MMMyyyy') as YearMonth,

Sales,

SalesPerson

;

LOAD * INLINE [

YearMonth, Sales, SalesPerson

jan2012, 100, John

feb2012, 250, James

mar2012, 300, Tim

jan2013, 400, Tom

feb2013, 500, Hatson

mar2013, 600, Adsr

];