topic Help with data in QlikView

Help with data

hopkinsc — Tue, 15 May 2012 17:09:53 GMT

Hi, a client has asked for help..

They have an excel spreadsheet with cost centers, expense codes, values and descriptions on (See example attachment) As you can see from the attachment, the Cost Centres and Expense Codes are seperated out into FROM and TO columns.

I need to make these a single column for each.

So for example..

Instead of it being

Cost Center Expense Code

FROM TO FROM TO

101 101 800 801

101 101 802 899

102 103 900 950

I need it to be..

Cost Center Expense Codes

101 800

101 801

101 802

101 803 (DOWN TO 899)

101 899

102 900

102 901 ETC

Can anyone tell me if there is an easy way to do this please.

Help with data

Oleg_Troyansky — Tue, 15 May 2012 17:48:09 GMT

Use the WHILE load. You will have to reload the table twice using WHILE - once to generate Expense Codes and the second time to generate the Cost Centers.

Help with data

hopkinsc — Tue, 15 May 2012 20:09:12 GMT

Hi Oleg, thanks.I have never used the while load before... can you explain how it works please?

Help with data

tomhoch1 — Tue, 15 May 2012 21:16:30 GMT

If I understand you correctly, something like this my work:

Example:

LOAD From,

To,

From1,

To1,

Value,

Desc

FROM

Example.xlsx

(ooxml, embedded labels, table is Sheet1);

Let rows = NoOfRows('Example');

for i = 0 to $(rows)-1

for cc = Peek('From',$(i), 'Example') to Peek('To',$(i), 'Example')

for ec = Peek('From1',$(i), 'Example') to Peek('To1',$(i), 'Example')

NewTable:

Load *

Inline [CC, EC

$(cc), $(ec)];

Help with data

hopkinsc — Wed, 16 May 2012 07:49:20 GMT

Hi Tomhoch, This seems to do what i want but i don't understand it, can you please break it down for me to help me understand what it does?

Help with data

hopkinsc — Wed, 16 May 2012 09:28:24 GMT

Can anyone break this down for me please?

Help with data

tomhoch1 — Wed, 16 May 2012 12:29:14 GMT

There is probably a more elegant way but let me know if this helps

// Get number of rows from table Example

Let rows = NoOfRows('Example');

// Loop over each record from Example - Peek command is zero based

for i = 0 to $(rows)-1

// Get Cost Center Range for each record and loop over it

for cc = Peek('From',$(i), 'Example') to Peek('To',$(i), 'Example')

// Get Expense Code Range for current record and loop over it

for ec = Peek('From1',$(i), 'Example') to Peek('To1',$(i), 'Example')

// Create "NewTable" and automatically concatenate the new Cost

// Center and Expense code values

NewTable:

Load *

Inline [CC, EC

$(cc), $(ec)];

next // Expense Code in range

next // Cost Center in range

next // Record from Example table

Re: Help with data

hopkinsc — Wed, 16 May 2012 13:02:59 GMT

Hi Tomhoch,

The new inline tables have no links to the original tables.

If i rename the inline tables from CC and EC to From and From 1, this links them but they do not contain the To or To1 values...

So for example,

In the Excel spreadsheet attached, FUEL has Expense Codes from 800 to 801. but When you select FUEL on the attached QVW only 800 is linked in the new inline table created for Expense Codes (From1).

800 and 801 should be white at this point. 801 is appearing in the 'To1' list boxe

Am i missing something?

Thanks

Chris

Re: Help with data

tomhoch1 — Wed, 16 May 2012 14:18:53 GMT

I wasn't clear how on relationship. See if this is what you are looking for.

Re: Help with data

hopkinsc — Wed, 16 May 2012 15:39:08 GMT

Hi again.

That worked for the test data i sent you. I have just received ACTUAL data from the client (I have been waiting for this since Monday!!).

I have now amended the script to use the actual data but i can not get it working! It must be something i am doing wrong.

If you have some time could you take a look at it to see what i am doing wrong please?

Re: Help with data

hopkinsc — Wed, 16 May 2012 17:09:21 GMT

I am also not getting any info in the new table EC...