Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kfahri342
Contributor III
Contributor III

How to count text within a field

Hi Qlikers,

I have the underlying data

TypeDate [2018]
Type 1Jan
Type 1 | Type 2 | Type 4Feb
Type 3 | Type 4Mar
Type 1 | Type 3 | Type 4Jan
Type 1Feb

and I need Qlikview to show this:

TypeJanFebMar
Type 122
Type 21
Type 311
Type 4111

I thought Mapping would work, but there seems to be a conflict when the same type rolls up into the same name.

Any Ideas?

Many Thanks...Again

Kozan

10 Replies
PrashantSangle

Hi,

In backend use trim(subfield(Type, '|')) as Type

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
qv_testing
Specialist II
Specialist II

Try this,

CountText:

Load *, Trim(subfield(Type, '|')) as NewType;

LOAD * INLINE [

Type, Month

    Type 1, Jan

    Type 1 | Type 2 | Type 4, Feb

    Type 3 | Type 4, Mar

    Type 1 | Type 3 | Type 4, Jan

    Type 1, Feb

];


Load NewType,

Month

Resident CountText;


DROP Table CountText;


Take Pivot table Dimensions = NewType and Month

Expression = count(NewType)

kfahri342
Contributor III
Contributor III
Author

Thanks Raju,

However this seems restricted - the document I am working on have over 3000 entries and that can change over time. Is there a way to create the above that can take this into account?

Kozan

kfahri342
Contributor III
Contributor III
Author

Hi Prashant,

Apologies, my knowledge on Qlikview isn't the best. Are you able to elaborate on this.

Also, the data above is only a sample - the document I am actually working on has far more entries that can change over time.

PrashantSangle

Hi,

You have to implement script as qv_testing‌ suggested. Just try what he suggested if it is not working then let us know.

Regards,

Prashant

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Peter_Cammaert
Partner - Champion III
Partner - Champion III

That was just an example using the data you provided in your OP embedded in an INLINE LOAD.

Replace the INLINE [...] part with a FROM YourSource clause and the data processing will follow whatever is present in your source data at a particular point in time.

kfahri342
Contributor III
Contributor III
Author

Thanks, I have tried the above - but I don't seem to get the NewType in my dimensions.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

If you want us to help with the integration of this solution into your script, post your script or an example document in this thread. Thanks

kfahri342
Contributor III
Contributor III
Author

I wish I could. Unfortunately it is work related and therefore strictly prohibited.

Is it possible to have the formula recreated without the Inline piece? Lets say my underlying data is called Database.xls for example.

Perhaps that is where I am going wrong