topic Re: Showing Values on Condition in QlikView in QlikView

Showing Values on Condition in QlikView

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Showing Values on Condition in QlikView

sunny_talwar — Thu, 07 Dec 2017 12:14:31 GMT

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;

Re: Showing Values on Condition in QlikView

big_dreams — Thu, 07 Dec 2017 12:23:51 GMT

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,

Re: Showing Values on Condition in QlikView

big_dreams — Thu, 07 Dec 2017 12:28:43 GMT

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.

Re: Showing Values on Condition in QlikView

Anonymous — Thu, 07 Dec 2017 12:45:34 GMT

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

Re: Showing Values on Condition in QlikView

sunny_talwar — Thu, 07 Dec 2017 12:47:29 GMT

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;