topic mapping in QlikView

mapping

Thu, 06 May 2010 15:57:40 GMT

Hi,

in this case:
A::

load * Inline [
acl , cle , description , amount
2102 , 2102 , invoice, 0
62147 ,62147, payment, 100

];

I need to replace the description called Invoice by Description called Payment

is it possible by Mapping?

Thanks

Slash

mapping

johnw — Thu, 06 May 2010 22:25:03 GMT

Are you trying to end up with this?

acl, cle, description, amount
2102, 2102, payment, 0
62147, 62147, payment, 100

If so, you can do it just with an if():

if(description='invoice','payment',description) as description

But you can also do it with mapping:

[Description Map]:
LOAD * INLINE [
From, To
invoice, payment
];
...
applymap('Description Map',description) as description

For me, it's a matter of how many entries I'm expecting to have. If I'm only mapping a few values, I'll just use if() or maybe pick(match()). If I'm mapping a lot of values, I'll use a map.

But I could also be completely misunderstanding what you're trying to accomplish.

mapping

Fri, 07 May 2010 09:51:58 GMT

Thnks but is not too easy the problem I need to solve.

If I put the value ina table Streight I obtain:
Please look the attached file.

the total in chart MUST be distinct
I MUST to use the straight table and not the pivot becouse I need to sum by column

I need to obtain all in one row LIKE THSI:

__________________________________

CUSTOMER | description |debit| credit|
-------------------------------------------------------------
AAA | Invoice 12345 |100 | 100 |
----------------------------------------------------------------
total 100 | 100 |
--------------------------------------------------------------

rgrds,

mapping

spsrk_84 — Fri, 07 May 2010 10:29:20 GMT

Hi,

Through Mapping it is possible .But, i am not sure whethter it can be applied on INline Tables

Create a Mapping Inline Table like this..

Map_Pay_Desc:

Mapping Load * Inline

[ Description ,NewDescription

Inventory, Payment,

Payment, Payment

]

Temp:

Load

acl ,

cle ,

ApplyMap( ' Map_Pay_Desc',description ) as Desc,

amount

Resident A;

Try in this way,i hope ur problem is solved..

Regards,

Ajay

mapping

Fri, 07 May 2010 14:02:36 GMT

look in attached file: doesn't work

thanks

Slash

mapping

johnw — Fri, 07 May 2010 18:03:56 GMT


Slash69 wrote: I need to obtain all in one row LIKE THSI:
CUSTOMER | description |debit| credit|
------------------------------------------------
AAA | Invoice 12345 |100 | 100 |
------------------------------------------------
total 100 | 100 |
------------------------------------------------

Sorry, but if you're trying to join those two rows together, I don't see anything reliable to join on. Applying payments to invoices requires, I believe, one of two things:

The payment to specify the invoice it is paying (or some other ID that can be tied back to the invoice, like a shipment ID if your invoices are always for whole or multiple whole shipments).
An algorithm to allocate payments to invoices, such as applying each payment to the oldest invoice for that customer that still has an outstanding balance due.

Your example data meets neither of these requirements. The only thing in common is a customer ID. That tells me that you need an algorithm for applying payments.

And if you're using an algorithm to apply payments to invoices, I STRONGLY suggest that this be done in your business system. QlikView is a REPORTING system. It is not designed to actually MANAGE your accounts receivable. So apply payments in the business system, and have the business system EXPLICITLY state which invoice a payment is being applied to, or if it is a many to many relationship, have the business system build a table with a payment ID, invoice ID, and the amount of that payment being applied to that invoice. Either way, now you have an explicit key to join these rows together if desired.