topic Re: Wildmatch over multiple fields in QlikView

Wildmatch over multiple fields

Karl_Hart — Wed, 25 Nov 2020 16:16:04 GMT

I have the following expression in a text box.

=sum([NS D1 Desk])

+sum([NS D1 Facilities])

+sum([NS D1 Hours])

+sum([NS D1 HRBP])

+sum([NS D1 Induction])

+sum([NS D1 Inductionmeeting])

+sum([NS D1 Introduced])

+sum([NS D1 Login])

+sum([NS D1 ManualHandling])

+sum([NS D1 Officetour])

+sum([NS D1 Password])

+sum([NS D1 Reception])

+sum([NS D1 Uniform])

Is there a way to use a wildcard to have it something like

=sum([NS D1 *])

For reference, the data looks something like

Name ND D1 Desk NS D1 Facilities NS D1 Hours .........

Adam 1 0 1

Betty 1 1 0

Carl 1 1 1

Denise 0 0 1

So the answer in this made up example would be sum([NS D1 *]) = 8

Thanks,

Karl

Re: Wildmatch over multiple fields

prma7799 — Wed, 27 Sep 2017 11:31:43 GMT

Is there a way to use a wildcard to have it something like

=sum([NS D1 *])

I think No

Re: Wildmatch over multiple fields

Anil_Babu_Samineni — Wed, 27 Sep 2017 11:46:37 GMT

How come 8 it should 5, Right?

Re: Wildmatch over multiple fields

Anil_Babu_Samineni — Wed, 27 Sep 2017 11:48:30 GMT

May be this?

Cross:

CrossTable(Files, Data,1)

LOAD * Inline [

Name, ND D1 Desk , NS D1 Facilities , NS D1 Hours , ND Real

Adam , 1 , 0 , 1, 1

Betty , 1 , 1 , 0 , 2

Carl , 1 , 1 , 1 , 3

Denise , 0 , 0 , 1, 4

];

Output:

Re: Wildmatch over multiple fields

sunny_talwar — Wed, 27 Sep 2017 11:58:47 GMT

One option would be to do something like this

Sum($(=Concat({<$Field = {"NS D1 *"}>} '[' & $Field & ']', '+')))

Re: Wildmatch over multiple fields

Karl_Hart — Wed, 27 Sep 2017 11:59:12 GMT

There was a typo in my original data, it should have said NS D1 Desk, but given that, yes 5 is the correct answer.

Can I do a Crosstable when loading from a QVD file?

Re: Wildmatch over multiple fields

Peter_Cammaert — Wed, 27 Sep 2017 12:38:54 GMT

Yes.

Make sure that your LOAD FROM QVD is unoptimized.

Re: Wildmatch over multiple fields

sunny_talwar — Wed, 27 Sep 2017 12:45:29 GMT

Hey Peter -

Why in optimized load creates issue with CrossTable? is this a bug or by design?

Re: Wildmatch over multiple fields

Peter_Cammaert — Wed, 27 Sep 2017 20:43:29 GMT

Hi Sunny,

it's probably something inbetween called "WAD" (Works as Designed) and I'm not inventing anything here; that's official Qlik R&D terminology. Reading Section Access data from a QVD using an optimised Load has the same problem.

Now that I think about it: I'm not sure whether thiese statements are still valid in v12.xx. Didn't test it in the latest releases. But better safe than sorry

Peter

Re: Wildmatch over multiple fields

sunny_talwar — Wed, 27 Sep 2017 20:48:29 GMT

I did a test on the v12 and it still seems to give me some weird output....So, I guess that this is still an issue

Re: Wildmatch over multiple fields

sunny_talwar — Wed, 27 Sep 2017 20:51:30 GMT

So, I tried this

Table:

LOAD * INLINE [

Dim, 2016, 2017

A, 20, 40

B, 30, 65

];

STORE Table into Table.qvd (qvd);

DROP Table Table;

Table:

CrossTable(Year, Value)

LOAD *

FROM Table.qvd (qvd);

Gave me this

While this worked without any issues

Table:

LOAD * INLINE [

Dim, 2016, 2017

A, 20, 40

B, 30, 65

];

STORE Table into Table.qvd (qvd);

DROP Table Table;

Table:

CrossTable(Year, Value)

LOAD *

FROM Table.qvd (qvd)

Where 1 = 1;

Re: Wildmatch over multiple fields

Peter_Cammaert — Wed, 27 Sep 2017 21:36:40 GMT

Yeah, orphans all over the place. You really don't want that in your data model.

Still the same as it used to be.

Re: Wildmatch over multiple fields

Karl_Hart — Thu, 28 Sep 2017 09:26:35 GMT

Thank you, this worked perfectly