Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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;

1 Solution

Accepted Solutions
sunny_talwar

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
sunny_talwar

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
Creator III
Creator III

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
Creator III
Creator III

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.

Anonymous
Not applicable
Author

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

sunny_talwar

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;