Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try something like this:
if(len(KDMAT)=8 and isnum(KDMAT), '0000000000' & KDMAT, KDMAT) as KDMAT
- Marcus
Try something like this:
if(len(KDMAT)=8 and isnum(KDMAT), '0000000000' & KDMAT, KDMAT) as KDMAT
- Marcus
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
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
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;
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
Marcus_Sommer thanks a lot master for advice
swuehl your advice very helpful
I have tried all advice and worked in my qlikview
ry88