Expression

[#1]

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

[marcus_sommer · Accepted solution]

Try something like this:

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

- Marcus

[swuehl]

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

or

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

[marcus_sommer]

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

1

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

[swuehl]

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;

[marcus_sommer]

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

[#7]

Marcus_Sommer‌ thanks a lot master for advice

swuehl‌ your advice very helpful

I have tried all advice and worked in my qlikview

ry88