topic Re: [Help!] How can i create a value to an existing variable and merge to another!? in QlikView

[Help!] How can i create a value to an existing variable and merge to another!?

Tue, 28 Jun 2011 19:49:58 GMT

Hi everyone!

I have a problem here that might be fun for you guys. I am extracting data from SAP normally, and the data from SAP contains a several columns. I am inserting another linked table too, but this table has a plus column called TYPE and it was supposed to hve the data if it is a REAL expenditure or a PROVISIONED one.

It happens that nether the SAP have this field, nor can i remove it from the file (cus its usefull on my avaliation). Also, on the provision file, this field contains only Provision on its string value. I need to fullfill this field comming from SAP's QVD (expanditures.qvd), creating a column called TYPE with the value REAL and merge with the provisioned one. Like this one:

LOAD

Description,

Value,

Cost Center

FROM Expanditures.qvd

LOAD

Name as [Description],

Value,

Cost Center,

TYPE

FROM Provision.xlsx

I am new to Qlikview, can anyone help me?!

Thank you very much.

Bruno.

Re: [Help!] How can i create a value to an existing variable and merge to another!?

johnw — Tue, 28 Jun 2011 19:57:20 GMT

I believe something like this:

MyTable:
LOAD
Description
,Value
,"Cost Center"
FROM Expenditures.qvd (QVD)
;
LEFT JOIN (MyTable)
LOAD
Name as Description
,Value
,"Cost Center"
,TYPE
FROM Provision.xlsx (...stuff here...)
;

But it kind of depends on what you mean by "merge".

Re: [Help!] How can i create a value to an existing variable and merge to another!?

Fri, 01 Jul 2011 15:34:28 GMT

Thank you for your time, but i dont know if u understood me.

I hve a .QVD that i cant touch or modify, thats my data from SAP. This data does not contain any value about the type of expenditure, cus obviously its a real expenditure, but my qlikview doesnt understand it.

Early this year we've done a provision of the expenditures for the entire corp, so all "cost centers"/departments hve a specific budget to attain, and this file (.xlsx) hve a collumn called TYPE that it has last year's provision, last year's real expenditures and this year's provision, so we hve 3 kinds of datas to see on qlikview in this field (Provision 2010, Real 2010 and Provision 2011).

It happens that if i load all the files together (SAP and xlsx) to achiev a high lvl controlling of the costs, linking by 3 specific variables, i always hve a scrambled data, cus the data from SAP comes with a "-" (null value) on the field TYPE, meanwhile the field TYPE from the xlsx has 3 possible values. I just want to this field, TYPE, to hve a value, like "Real 2011" from the data comming from SAP, so i can see and link him between tables too, and achiev my goal.

I belive the last post was helpfull and i appreciate it, but i dunno if it will work cus it seems i hve to hve another table, in wich i dont hve.

And sorry about my english, it must been a challenge to read it, it happens i'm a brazilian worker working in a deutchern/spanish industry.

Again, Thank you and hope u guys can help me!

Cheers!!

Re: [Help!] How can i create a value to an existing variable and merge to another!?

johnw — Fri, 01 Jul 2011 17:24:05 GMT

As best I understand what you're saying, my proposed solution is correct. There's no new table there. The [MyTable] table is whatever YOU happened to name it when you loaded the data from the QVD. But I NEED to know the name in order to left join to it, so I made up a name. Use your own table name wherever I use [MyTable].

Re: [Help!] How can i create a value to an existing variable and merge to another!?

Fri, 01 Jul 2011 20:35:10 GMT

ok. Thank you and sorry.

Re: [Help!] How can i create a value to an existing variable and merge to another!?

Mon, 04 Jul 2011 04:03:38 GMT

You have to amend your script like this:

LOAD Description,

Value,

Cost Center,

'REAL' as TYPE

FROM Expanditures.qvd;

Concatenate

LOAD Name as [Description],

Value,

Cost Center,

TYPE

FROM Provision.xlsx

Hope this works for you.