topic Re: Expression in QlikView

Expression

Thu, 28 Jan 2016 03:47:37 GMT

Hi all master Qlikview

I have problem with qlickview

I have table from SAP like that

KDMAT VBELN POSNR

20086689 1111000 010

70000333 1199922 010

000000000076783954 1540000 020

PRTS897667 2920002 030

i want to check string from field KDMAT, if the length is 8 and only contain number

i want to concatenate this value with 0000000000

so this field become 000000000020086689

and modify and store to new QVD

Next Table like that :

KDMAT VBELN POSNR

000000000020086689 1111000 010

000000000070000333 1199922 010

000000000076783954 1540000 020

PRTS897667 2920002 030

Please help me master

ry88

Re: Expression

marcus_sommer — Thu, 28 Jan 2016 14:51:13 GMT

Try something like this:

if(len(KDMAT)=8 and isnum(KDMAT), '0000000000' & KDMAT, KDMAT) as KDMAT

- Marcus

Re: Expression

swuehl — Thu, 28 Jan 2016 14:56:16 GMT

Marcus, I am unsure if this will change the text representation for an existing numeric value, so maybe we need something like

if(len(KDMAT)=8 and isnum(KDMAT), Num(KDMAT,'000000000000000000'), KDMAT) as KDMAT

if(len(KDMAT)=8 and isnum(KDMAT), Text( '0000000000' & KDMAT), KDMAT) as KDMAT

Re: Expression

marcus_sommer — Thu, 28 Jan 2016 15:13:22 GMT

After your remark I'm not sure how qv will interpret this - normally a concat between two values results in a string but fields will be normally interpreted by the format from the very first loading-value of those field - therefore I will make a test:

Load if(len(KDMAT)=8 and isnum(KDMAT), '0000000000' & KDMAT, KDMAT) as KDMAT inline [

KDMAT

20086689

70000333

000000000076783954

PRTS897667

20086689

];

and result is:

Therefore the simple string-concat seems to work, but your solutions ensure that the output is in the wanted format.

- Marcus

Re: Expression

swuehl — Thu, 28 Jan 2016 15:42:43 GMT

I think there are two things to look at in your example:

I don't think that the Format of the KDMAT values have been set before the Input records get into your if() statement, I was more thinking about a resident load, where the same numeric value has been loaded before.

Second, I forgot that QV will not interpret strings with more than 14 Digits as numbers, so this specific format will always result in a string. But what if we just add 4 leading zeros?

So just out of curiosity (because it's not the scenario the OP described), try to run this:

T:
Load if(len(KDMAT)=8 and isnum(KDMAT), '0000' & KDMAT, KDMAT) as KDMAT, KDMAT as KDMAT2 inline [
KDMAT
1
20086689
70000333
000000000076783954
PRTS897667
20086689
];

LOAD KDMAT, if(len(KDMAT2)=8 and isnum(KDMAT2), '0000' & KDMAT2, KDMAT2) as KDMAT2
Resident T;

Re: Expression

marcus_sommer — Thu, 28 Jan 2016 16:11:44 GMT

With Inline-Load and four zeros the result looked:

With resident-load the leading zeros are gone:

I also tested the num() approach with resident and leading zeros didn't appear so that only your second text() approach worked. This meant one must be very carefully by handling leading zeros.

- Marcus

Re: Expression

Fri, 29 Jan 2016 03:11:09 GMT

Marcus_Sommer‌ thanks a lot master for advice

swuehl‌ your advice very helpful

I have tried all advice and worked in my qlikview

ry88