topic Re: custom list box in QlikView

custom list box

Sat, 01 Aug 2015 10:34:11 GMT

hi every one

i have a problem,

i have month dimension with 1,2,3,...,12 value

i want to create a list box show that

فروردین

اردیبهشت

خرداد

تیر

مرداد

شهریور

مهر

آبان

آذر

دی

بهمن

اسفند

sort فروردین to اسفند is very important,

can anyone help me?

Re: custom list box

swuehl — Sat, 01 Aug 2015 10:38:59 GMT

Use a dual value when creating your Month values, either by using QV date and time functions like Month() or by explicitely using dual(), like

LOAD dual(F1, recno()) as Month INLINE [

فروردین

اردیبهشت

خرداد

تیر

مرداد

شهریور

مهر

آبان

آذر

دی

بهمن

اسفند

];

Re: custom list box

Sat, 01 Aug 2015 12:02:03 GMT

tanks swuehl

it's work lonely but

it's not work,i type your script in my Qv Script

i connect to sql source for my reports,

when i reload my doucument , i see the below error :

can you help me more ?

with best regards

Re: custom list box

Sat, 01 Aug 2015 12:15:45 GMT

i solve my recent problem

i forgot to put ; (semicolon) before load in my script ,

now how can i relate this month to related code?

my month code are : 1 ... 12

my month name is فروردین .... اسفند

relationships are :

1 = فروردین

2 = اردیبهشت

3 = خرداد

12 = اسفند

can you help me more ?

thanx swuehl

Re: custom list box

swuehl — Sat, 01 Aug 2015 12:26:28 GMT

Use a preceding LOAD to your SQL code:

(I am using english names, just replace with your field name and values)

LOAD

//List all your field names except the MonthName field first

Field1,

Field2,

Dual( MonthName,

Match( MonthName, 'Jan', 'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') )

AS MonthName

;

SQL ... FROM ...;

Re: custom list box

Sun, 02 Aug 2015 05:21:24 GMT

unfortunately it's not work:

my script is :

LOAD

[ردیف]

,[کد کل]

,[شرح کل]

,[کد معین]

,[شرح معین]

,[کد تفصیلی 1]

,[شرح تفصیلی 1]

,[کد تفصیلی 2]

,[شرح تفصیلی 2]

,[کد تفصیلی 3]

,[شرح تفصیلی 3]

,[مبلغ بدهکار]

,[مبلغ بستانکار]

,[شرح سند]

,[شماره سند]

,[کد نوع سند]

,[شرح نوع سند]

,[کد بندر]

,[سال سند]

,[روز سند],

Dual( [ماه سند],

Match( [ماه سند], 'فروردین','اردیبهشت','خرداد','تیر','مرداد','شهریور','مهر','آبان','آذر','دی','بهمن','اسفند') )

AS [ماه سند]

;

SQL select

[ردیف]

,[کد کل]

,[شرح کل]

,[کد معین]

,[شرح معین]

,[کد تفصیلی 1]

,[شرح تفصیلی 1]

,[کد تفصیلی 2]

,[شرح تفصیلی 2]

,[کد تفصیلی 3]

,[شرح تفصیلی 3]

,[مبلغ بدهکار]

,[مبلغ بستانکار]

,[شرح سند]

,[شماره سند]

,[کد نوع سند]

,[شرح نوع سند]

,[کد بندر]

,[سال سند]

,[ماه سند]

,[روز سند]

FROM [SimaAccountHolding].[dbo].[Acn4BI] a

where a.[کد کل] in(81,82,83,84,85,89,71,72,73,74,75,76)

and a.[کد بندر] in (1153,10243,10244,10372,10373,10374,10375,10376,10377,10378,10401,103078,3304,3325,10379,10380)

and a.[کد نوع سند] <>6;

is it true?

Re: custom list box

swuehl — Sun, 02 Aug 2015 09:28:26 GMT

Hm, I am not sure, I can't read your letters

First argument to Dual() function should be a field name, not quoted using single quotes, but maybe embedded in [...] like the other field names in the LOAD.

Same for first argument to Match(), a (in fact the same as before) field name, embedded in []. Looks like this field name is last in your argument list, not first (and not embedded in [..].

All other 12 arguments to Match() should be literals, not field names. Quote literals using single quotes. The literals are giving the exact month values as they appear in the field from above (the one you want to sort correctly).

In english:

Dual( [MonthFieldName],

Match( [MonthFieldName], 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')

) AS [MonthfieldName]

So take care to embed the Month field name in [..], all literals in single quotes. And take care that the values you are matching ('January', ...) are the values as they exactely appear in your field.

One more thing?

Is this field a key field or are you concatenating more records after above LOAD? Do we need to take care of other places you are loading values to that field?