topic Re: Grouping Dimension Values in QlikView

Grouping Dimension Values

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to have account type as my dimension. account type has value A, B, C, D, E, F, G

What If I want to have A. B, C as Group 1. B, C, E as Group 2, E, F, G as Group 3?

it does overlap and I know that. but I want to have three dimensions Group 1, Group 2, Group3 and as described above they are combinations of original values.\

Thanks!

David

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 20:40:58 GMT

May be create a linktable in the script:

LOAD * Inline [

Group, Account Type

Group 1, A

Group 1, B

Group 1, C

Group 2, B

Group 2, C

Group 2, E

Group 3, E

Group 4, F

Group 5, G

];

Re: Grouping Dimension Values

Mon, 29 Aug 2016 20:46:59 GMT

THank you Sunny!

Why haven't I thought of this. will try right now

Re: Grouping Dimension Values

Mon, 29 Aug 2016 20:55:25 GMT

Hi Sunny,

It is partly working bur partly not.

It is not being synchronized with existing values.

so the inline table to just on its own.

How can I link them up?

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 20:59:21 GMT

Have you made sure that Account Type is spelled the same was as it is in your database?

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 21:00:12 GMT

Spelled and even the casing should also be the exact same

Account Type is not the same thing as account type in QlikView

Re: Grouping Dimension Values

Mon, 29 Aug 2016 21:06:06 GMT

I used qualify. so should I use the changed name or original name?

Re: Grouping Dimension Values

Mon, 29 Aug 2016 21:07:21 GMT

just one more thing. account type / and group are both not in quotation mark?

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 21:10:57 GMT

What name do you see for your field in the data model viewer or when yoy create a list box for it? Use the same exact name in the inline load.

Re: Grouping Dimension Values

Mon, 29 Aug 2016 21:17:27 GMT

Yes I did just that. Now there is one thing that i am suspecting that is the reason for this.

I am currently pulling the data directly from database without going through QVD.

is that why this is not working?

I have found a little difference between using QVD and direction connection.

when I used QVD it was so easy to just do something like adding a line that says

1 as count

and it there will be another column but it does not work when I am connecting directly to database.

Can this be the reason?

Thanks!

David

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 21:20:00 GMT

I don't believe so. Would you be able to share your script?

Re: Grouping Dimension Values

Mon, 29 Aug 2016 21:28:49 GMT

Hi Sunny,

I would love to share but I cannot share everything so Please be understanding.

but there are all the information that you need.

you can the there is SQL in there and I am pulling directly from DB.

and ACCNT_VERSION is the column name.

and in Load * Inline

Versions : is the new group name that I am assigning and

ACCNT_VERSION is the existing original value. I have used Qualify for the original table so I have additional table name and a dot in front of ACCNT_VERSION

I cannot figure out what I did wrong. but still the entire table and and the inline table is still separate.

it is also verified in data model.

Please help!

Thank you very much

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 22:09:21 GMT

Do you see any differences between the new field and old field name? I understand the need for confidentiality, but the fields not connecting is something which you will have to check on your end. In the example below you will see that the two tables are auto connected because they have the same field name

But doesn't connect if anything between them doesn't match

Re: Grouping Dimension Values

Mon, 29 Aug 2016 23:17:56 GMT

Thank you Sunny you are the best.

I found out that the qualify that I used for the first table was affecting the second column as well resulting in adding a table name to the second inline table.

I used unqualify for the inline table and now those two are well hooked up!

Thank you so much!!

@sunny T

Re: Grouping Dimension Values

sunny_talwar — Mon, 29 Aug 2016 23:27:01 GMT

Awesome

I am glad it worked out for you.

Best,

Sunny

Re: Grouping Dimension Values

jason_nicholas — Tue, 15 Nov 2016 19:08:20 GMT

I am having the same issue, but feel I must be missing something from this answer. I would appreciate it if you could take a look and see if it is clear where I went wrong.

Just as David has, I have a number of entries in my source data which I want to put into groups. In my case, my data has a "Building Time Zone" listed with reference cities, but I want to turn these into regions.

ie- GMT is listed as "Dublin, Edinburgh, Lisbon, London" (all four cities are included in a single entry). I want to combine this entry with two other similar entries into a group called EMEA. I also want to group the North America and APAC entries together.

Based on the answer given here, I have the following script. I put a strikethrough on the unrelated data for clarity. NA, EMEA, and APAC are my desired group names, and they are followed by the specific text which appears in my source data.

LOAD
[Booking ID],
[Booking Date],
[Booking Event Name],
[Building Time Zone],
Category,
Resource,
Notes

FROM
[October NEW AV Support Query.xls]
(biff, embedded labels)

Load * inline[
Group, [Building Time Zone]
NA, Arizona
NA, [Central Time]
NA, [Eastern Time]
NA, [Pacific Time]
EMEA, [Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna]
EMEA, [Brussels, Copenhagen, Madrid, Paris]
EMEA, [Dublin, Edinburgh, Lisbon, London]
APAC, [Beijing, Chongquing, Hong Kong, Urumqi]
APAC, [Calcutta, Chennai, Mumbai, New Delhi]
APAC, [Kuala Lumpur, Singapore]
];

Re: Grouping Dimension Values

sunny_talwar — Tue, 15 Nov 2016 19:12:08 GMT

What is the exact issue you are running into? Script not running or the data is not getting mapped correctly?

Re: Grouping Dimension Values

jason_nicholas — Tue, 15 Nov 2016 19:14:18 GMT

when I hit Reload, I get the message:

Garbage after statement

LOAD.....

then goes on to show exactly what I have above, without the closing ;

Re: Grouping Dimension Values

sunny_talwar — Tue, 15 Nov 2016 19:16:11 GMT

I think the issue is related to a missed space between the word inline and the square bracket

Try with this:

Load * inline [
Group, [Building Time Zone]
NA, Arizona
NA, [Central Time]
NA, [Eastern Time]
NA, [Pacific Time]
EMEA, [Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna]
EMEA, [Brussels, Copenhagen, Madrid, Paris]
EMEA, [Dublin, Edinburgh, Lisbon, London]
APAC, [Beijing, Chongquing, Hong Kong, Urumqi]
APAC, [Calcutta, Chennai, Mumbai, New Delhi]
APAC, [Kuala Lumpur, Singapore]
];

Re: Grouping Dimension Values

jason_nicholas — Tue, 15 Nov 2016 19:45:46 GMT

Unfortunately, adding the space did not help. I started fresh and copied the inline load statement directly from your response. Here is the new script. (previously, I edited out some of the columns I loaded for brevity. This time I left them in)

Directory;
LOAD [Reservation ID],
[Booking ID],
[Booking Date],
[Booking Event Name],
[Event Start],
[Event End],
[Building Time Zone],
Building,
[Room Code],
[Customer Host],
[Customer Host Type],
[Line of Business],
Category,
Resource,
Notes
FROM
[October NEW AV Support Query.xls]
(biff, embedded labels, table is Sheet$);

Load * inline [
Group, [Building Time Zone]
NA, Arizona
NA, [Central Time]
NA, [Eastern Time]
NA, [Pacific Time]
EMEA, [Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna]
EMEA, [Brussels, Copenhagen, Madrid, Paris]
EMEA, [Dublin, Edinburgh, Lisbon, London]
APAC, [Beijing, Chongquing, Hong Kong, Urumqi]
APAC, [Calcutta, Chennai, Mumbai, New Delhi]
APAC, [Kuala Lumpur, Singapore]
];

and I get

Garbage after statement

Load * inline [

Group, [Building Time Zone]

NA, Arizona

NA, [Central Time]

NA, [Eastern Time]

NA, [Pacific Time]

EMEA, [Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna]

EMEA, [Brussels, Copenhagen, Madrid, Paris]

EMEA, [Dublin, Edinburgh, Lisbon, London]

APAC, [Beijing, Chongquing, Hong Kong, Urumqi]

APAC, [Calcutta, Chennai, Mumbai, New Delhi]

APAC, [Kuala Lumpur, Singapore]

]