Script on how to get data by first four number?

[#1]

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

[jagan]

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.

[Anonymous]

left function should help here

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

[#4 · Accepted solution]

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];

[#5]

Hi Wes,

This is perfect! Thank you so much!

- Ahyel