topic Re: Table Box in QlikView

Table Box

narender123 — Wed, 19 Jun 2013 08:42:39 GMT

Hi Team,

I have calculated a expression in script.

If(tax_code='cd',sum(tax)) as customs duty,

If(tax_code='vat',sum(tax)) as value added tax,

If(tax_code='ld',sum(tax)) as levy duty

so whane i fetch this tax in my table box with some dimention then it shows the results like this

customer_name customer_city customs duty value added tax levy duty

A goa 20 - -

A goa - 40 -

A goa - - 100

B delhi 60 - -

B delhi - 89 -

B delhi - - 123

As shown in eg. it shows the right result but it repeat the same dimention.I want customer
_name shown in 1 rows means not repeated where the dimentions are same as it shows repeating data.

Thanks

Narender

Re: Table Box

narender123 — Wed, 19 Jun 2013 09:02:12 GMT

Means only remove customer city.But its not my solutuion.

Its repeating dimention .Why???

Thanks

Re: Table Box

er_mohit — Wed, 19 Jun 2013 09:07:49 GMT

i think you have use concatenate in your script for desired result

if yes then try to use join instead of concatenate

hope it helps

Re: Table Box

Wed, 19 Jun 2013 09:12:16 GMT

see attachement

LOAD *,

If(tax_code='cd',tax) as [customs duty],

If(tax_code='vat',tax) as [value added tax],

If(tax_code='id',tax) as [levy duty]

INLINE [

tax_code, customer_name, customer_city, tax

cd, A, goa, 20

vat, A, goa, 40

id, A, goa, 100

cd, B, delhi, 60

vat, B, delhi, 89

id, B, delhi, 123

];

then in pivot

dimension-- customer_name

expression1--customer_city

expression2--[customs duty]

expression3--[value added tax]

expression4--[levy duty]

then output like this

customer_name	customer_city	customs duty	value added tax	levy duty
A	goa	20	40	100
B	delhi	60	89	123

Re: Table Box

Wed, 19 Jun 2013 09:13:35 GMT

Try Like this,

Sum(If(tax_code='cd',tax,0)) as customs duty,

Sum(If(tax_code='vat',tax,0)) as value added tax,

Sum(If(tax_code='ld',tax,0)) as levy duty

For best performance.. Use set analysis instead if condition..

Karthik

Re: Table Box

narender123 — Wed, 19 Jun 2013 09:16:40 GMT

Thanks Mohit.

No Mohit there is no concatenation.

But if i use the same expression in straight table expression then it shows the correct result.But i have 30 column in my table so i have selected table box but unfortunately in table box we cann't write the expression so i have written the expression in script level means where we load the table.after that when i fetch the column from script then it repeat the data.

Please give me some idea to sort out this.

Re: Table Box

narender123 — Wed, 19 Jun 2013 09:18:54 GMT

Thanks Karthik,

But the problem is not from the result .But it shows the repeating result as i have already made this excpression in my script where we write the load statement.

Thanks.

Re: Table Box

narender123 — Wed, 19 Jun 2013 09:21:43 GMT

There is thousand of rows and 30 of columns .So its not possible.

Thanks