topic Re: Can Concat function take 'Load Order' as an argument? in QlikView

Can Concat function take 'Load Order' as an argument?

Anonymous — Mon, 01 Jun 2015 14:51:12 GMT

Hi,

my function is as follows:

=Concat(DISTINCT Month, ', ')

Be default the result is sorted in an alphabetic order - from A to Z.

I'd like to sort it by load order, so that Jan should come first.

I've found out the general description. It is possible to add a sort_weight.

concat([{set_expression}] [ distinct ] [ total [<fld {, fld}>]] expression[, delimiter[, sort_weight]])

But how exactly can define that sort_weight should equal load order?

Thank you in advance,

Larisa

Re: Can Concat function take 'Load Order' as an argument?

maxgro — Mon, 01 Jun 2015 15:23:56 GMT

maybe

add a rowno() as id when you load the months

use id as sort weight

Re: Can Concat function take 'Load Order' as an argument?

sunny_talwar — Mon, 01 Jun 2015 15:28:32 GMT

If month is created using a Month() function, I think this should also work:

=Concat(DISTINCT Month, ', ', Month)

Because Month is a dual function which is both Numeric as well as string.

HTH

Best,

Sunny

Re: Can Concat function take 'Load Order' as an argument?

Anonymous — Mon, 01 Jun 2015 15:49:00 GMT

Hi,

Actually, the name of the field in my model is the Russian equivalent of Month - Месяц.

I translated it in order to give you a clear example.

I'm I right that then it won't work?

Larisa

Re: Can Concat function take 'Load Order' as an argument?

sunny_talwar — Mon, 01 Jun 2015 15:54:49 GMT

Name of the field doesn't matter, what matter is are you using a Month() function to calculate the month or is it a text string? If it is a text string then you can go with the solution provided by Massimo Grossi‌, but if it is created using a month function then what I have suggested should work.

Note: Massimo's solution will work in the second scenario also

Best,

Sunny

Re: Can Concat function take 'Load Order' as an argument?

Anonymous — Mon, 01 Jun 2015 16:06:53 GMT

I see your point. Actually, the names of the Months are of a string format because they should be in Russian.

Nevertherless, thank you!

Re: Can Concat function take 'Load Order' as an argument?

sunny_talwar — Mon, 01 Jun 2015 16:11:09 GMT

No problem. The way to check, if you want to check, that if your Month Names are actually text string or dates is to create a chart with Month as Dimension and =Num(Month) as Expression. If you see numbers in the expression column then those are actually dates, if you see - then they are text strings.

Best,

Sunny

Re: Can Concat function take 'Load Order' as an argument?

Anonymous — Mon, 01 Jun 2015 16:21:11 GMT

I'd be greatful if you could tell me how I can convert a text field to a month format on the backfront.

I tried this way:

LOAD

Current_Month as Month(Current_Month),

...

It doesn't work.

Thank you in advacne,

Larisa

Re: Can Concat function take 'Load Order' as an argument?

sunny_talwar — Mon, 01 Jun 2015 16:29:32 GMT

Try this:

LOAD DUAL (MonthField, Pick(Match(MonthField, 'List the name of the months in the correct order separated with commas (for example, Jan, Feb, Mar)'), 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) as MonthField

Re: Can Concat function take 'Load Order' as an argument?

Anonymous — Mon, 01 Jun 2015 17:44:37 GMT

Thank you!

It works with a minor change.

There should be quotation marks for each month name: 'Jan', 'Feb', 'Mar'.

Re: Can Concat function take 'Load Order' as an argument?

sunny_talwar — Mon, 01 Jun 2015 17:47:03 GMT

Yup, I forgot to put the single quotes around it. Glad you caught my error