Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to do something in Qlikview. It goes as this:
Tab:
LOAD * INLINE [
code, supplier, docno, value
BQ1234, v1, 1200034215, 1000
BQ1234, v1, 1200041235, 500
BQ1234, v1, 2210034215, 1000
BQ1234, v2, 1200045612, 500
BQ1234, v2, 2210045612, 500
BQ1234, v3, 2210045211, 300
];
The Docno starting from '12' are old ones and those starting from '22' are the new ones. Now what i want is that if a the last 5 digits of a docno is present in both new and old docno, then the value in front of old one should become zero and the new one should show as it is in the final result.
For now what i am doing is:
Table1:
Load
Right(docno,5) as docno1,
value as value1
Resident Tab
where Left(docno,2)='22';
Let vfieldcount=FieldValueCount('docno1');
For i=0 to $(vfieldcount)-1
Let vFieldvalue=peek('docno1',$(i),'Table1');
Load
code,
supplier,
docno as docnoold,
//if(right(docno,5)=$(vFieldvalue),docno,docno) as docnoold,
if(right(docno,5)=$(vFieldvalue),0,value) as valuenew
Resident Tab
where Left(docno,2)='12';
NEXT i;
Concatenate
Load
code,
supplier,
docno as docnoold,
value as valuenew
Resident Tab
where Left(docno,2)<>'12';
Drop table Table1;
Drop table Tab;
EXIT Script;
Here you go
Table:
LOAD *,
Left(docno, 2) as Key1;
LOAD * INLINE [
code, supplier, docno, value
BQ1234, v1, 1200034215, 1000
BQ1234, v1, 1200041235, 500
BQ1234, v1, 2210034215, 1000
BQ1234, v2, 1200045612, 500
BQ1234, v2, 2210045612, 500
BQ1234, v3, 2210045211, 300
];
FinalTable:
LOAD *,
Right(docno, 5) as Key2
Resident Table
Where Key1 = 22;
Concatenate (FinalTable)
LOAD Key1,
code,
supplier,
docno,
If(not Exists(Key2, Right(docno, 5)), value, 0) as value,
Right(docno, 5) as Key2
Resident Table
Where Key1 = 12;
DROP Table Table;
Try this
Table:
LOAD *,
Left(docno, 2) as Key1;
LOAD * INLINE [
code, supplier, docno, value
BQ1234, v1, 1200034215, 1000
BQ1234, v1, 1200041235, 500
BQ1234, v1, 2210034215, 1000
BQ1234, v2, 1200045612, 500
BQ1234, v2, 2210045612, 500
BQ1234, v3, 2210045211, 300
];
FinalTable:
LOAD *,
Right(docno, 5) as Key2
Resident Table
Where Key1 = 22;
Concatenate (FinalTable)
LOAD *,
Right(docno, 5) as Key2
Resident Table
Where Key1 = 12 and not Exists(Key2, Right(docno, 5));
DROP Table Table;
Try below
Tab:
LOAD * INLINE [
code, supplier, docno, value
BQ1234, v1, 1200034215, 1000
BQ1234, v1, 1200041235, 500
BQ1234, v1, 2210034215, 1000
BQ1234, v2, 1200045612, 500
BQ1234, v2, 2210045612, 500
BQ1234, v3, 2210045211, 300
];
temp:
Load docno as new_doc,
right(docno,5) as testNewDoc
Resident Tab
where left(docno,2)='22';
left join(Tab)
temp1:
Load docno as docno,
right(docno,5) as testOldDoc ,
'1' as flag
Resident Tab
where left(docno,2)='12'
and Exists(testNewDoc,right(docno,5));
NoConcatenate
Final:
Load *,if(flag=1,0,value) as new_value Resident Tab;
Drop table Tab;
Regards,
I think there is one more requirement, is that Value need to 0 if Last 5 digit of old doc no and last 5 digit of new doc no is matches.
Hi Sunny,
This helps but i want the rows with zero value too i.e. i want the remaining two docno which you have excluded with their value as zero.
Thanks,
Abhishek
Here you go
Table:
LOAD *,
Left(docno, 2) as Key1;
LOAD * INLINE [
code, supplier, docno, value
BQ1234, v1, 1200034215, 1000
BQ1234, v1, 1200041235, 500
BQ1234, v1, 2210034215, 1000
BQ1234, v2, 1200045612, 500
BQ1234, v2, 2210045612, 500
BQ1234, v3, 2210045211, 300
];
FinalTable:
LOAD *,
Right(docno, 5) as Key2
Resident Table
Where Key1 = 22;
Concatenate (FinalTable)
LOAD Key1,
code,
supplier,
docno,
If(not Exists(Key2, Right(docno, 5)), value, 0) as value,
Right(docno, 5) as Key2
Resident Table
Where Key1 = 12;
DROP Table Table;