topic Re: Script on how to get data by first four number? in QlikView

Script on how to get data by first four number?

Fri, 07 Mar 2014 04:16:18 GMT

Hi,

Can someone help me on how to do the script for the query below?

Data Sample:

Clearing Doc Doc number

20001234 50029384

15002472 50028462

20009009 50029847

15001111 50048292

20008302 50092842

Query: If Clearing doc starts with 2000, then clearing doc = Payment Run, otherwise, Manual Check, (count doc number)

Thank you

- Ahyel

Re: Script on how to get data by first four number?

jagan — Fri, 07 Mar 2014 04:24:05 GMT

Hi,

Try like this

Data:

LOAD *,

If([Clearing Doc] Like '2000*', 'Payment Run', 'Manual Check') AS Type

INLINE [

Clearing Doc, Doc number

20001234, 50029384

15002472, 50028462

20009009, 50029847

15001111, 50048292

20008302, 50092842];

Payment Run Count:

Count({<Type={'Payment Run'}>} Clearing Doc)

Manual Check Count:

Count({<Type={'Manual Check'}>} Clearing Doc)

Regards,

Jagan.

Re: Script on how to get data by first four number?

Anonymous — Fri, 07 Mar 2014 04:24:18 GMT

left function should help here

if(left([Clearing Doc],4)=2000,'y','n')

Re: Script on how to get data by first four number?

Fri, 07 Mar 2014 04:25:34 GMT

Hi Ahyel,

I believe this is close to what you are looking for:

TableName:

Load

If (LEFT([Clearing Doc], 4) = 2000, [Payment Run], [Manual Check]) as FieldName

FROM [File/Source];

Re: Script on how to get data by first four number?

Fri, 07 Mar 2014 08:11:16 GMT

Hi Wes,

This is perfect! Thank you so much!

- Ahyel