Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Karl_Hart
Creator
Creator

Wildmatch over multiple fields

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

1 Solution

Accepted Solutions
sunny_talwar

One option would be to do something like this

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

View solution in original post

12 Replies
prma7799
Master III
Master III

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

=sum([NS D1 *])

I think No

Anil_Babu_Samineni

How come 8 it should 5, Right?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anil_Babu_Samineni

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:

Capture.PNG

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
sunny_talwar

One option would be to do something like this

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

Karl_Hart
Creator
Creator
Author

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?

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Yes.

Make sure that your LOAD FROM QVD is unoptimized.

sunny_talwar

Hey Peter -

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

sunny_talwar

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