topic Re: Restrict and create new field based on Length in QlikView

Restrict and create new field based on Length

MalcolmCICWF — Wed, 02 Mar 2016 16:41:35 GMT

I'm not sure how to word this, but I have an Excel spreadsheet that contains some account numbers that we need to use for reporting purposes. Because this field is manually entered by multiple agents, the data formatting/length is not uniform and contains an array of text and numbers.

Without going back and fixing the data, is there a way I can limit and pick out only the entries with 10 or less numbers (These are valid)? I have already removed all alpha characters from the mix, but I have data that looks like the below. Can I use the "LEN" function somehow? Anyone have some suggestions?

Re: Restrict and create new field based on Length

sunny_talwar — Wed, 02 Mar 2016 16:43:41 GMT

You sure can:

LOAD FieldNames

FROM Source

Where Len(Trim(A)) <= 10;

Re: Restrict and create new field based on Length

MalcolmCICWF — Wed, 02 Mar 2016 17:14:33 GMT

Doesnt work, using this to create a field and see what it contains, but I am getting "-1" and "0" as my only results.

LEN(TRIM(ACCTnum))<=10 as TEST2,

Re: Restrict and create new field based on Length

sunny_talwar — Wed, 02 Mar 2016 17:20:50 GMT

To create a field, try this:

If(Len(Trim(ACCTnum)) <= 10, ACCTnum) as TEST2

Re: Restrict and create new field based on Length

Colin-Albert — Wed, 02 Mar 2016 17:21:50 GMT

Perhaps create a straight table with ACCTnum as a dimension and then you can test your expression in the chart without needing to reload the data each time.

Try adding the expression len(trim(ACCTnum)) so you can compare the source data and output.

Re: Restrict and create new field based on Length

johnw — Wed, 02 Mar 2016 18:13:06 GMT

0 = false

-1 = true

Re: Restrict and create new field based on Length

MalcolmCICWF — Wed, 02 Mar 2016 22:26:27 GMT

yep, thanks, I am aware of this part.

Re: Restrict and create new field based on Length

MalcolmCICWF — Wed, 02 Mar 2016 22:30:21 GMT

alright, so this sort of seems to be working better.

I have one more question then, with the remaining accounts I have, is there a way to add leading zeros if the account is under 10 digits? I have some accounts left over that are under 10 digits that should have them. Simple formatting change in the field creation??

Ex. 456789 --------> 0000456789

Re: Restrict and create new field based on Length

sunny_talwar — Wed, 02 Mar 2016 22:33:35 GMT

Try this may be:

If(Len(Trim(ACCTnum)) <= 10, Num(ACCTnum, '0000000000')) as TEST2

Re: Restrict and create new field based on Length

MarcoWedel — Wed, 02 Mar 2016 23:03:00 GMT

Hi,

maybe

LOAD A
FROM YourExcel.xlsx (ooxml, no labels, table is Sheet1)
Where Num(A)<10000000000;

regards

Marco