Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
abhimalik
New Contributor III

Showing Values on Condition in QlikView

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;

Tags (2)
1 Solution

Accepted Solutions

Re: Showing Values on Condition in QlikView

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;

View solution in original post

5 Replies

Re: Showing Values on Condition in QlikView

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;

big_dreams
Contributor III

Re: Showing Values on Condition in QlikView

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,

big_dreams
Contributor III

Re: Showing Values on Condition in QlikView

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.

abhimalik
New Contributor III

Re: Showing Values on Condition in QlikView

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

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;

View solution in original post