topic Re: Data Layout in QlikView

Data Layout

Fri, 31 Oct 2014 17:10:21 GMT

Hello,

I'm working on cleaning up data and laying it out in the best possible format for qlikview and hit sort of a roadblock. Looking for clarification on how to best handle this kind of information. Here's an example

Date	Fulltime	Part-time	Contractor
01/01/2014	10	5	2
01/08/2014	5	2	0
01/15/2014	10	1	2

Now the twist to this data set that makes this a bit more complicated is this data is for only one project. I need this information for 3 other projects and what i'm doing is trying to create a single line chart with other list boxes that can help narrow in on a single project. Any feedback would be greatly appreciated. Thanks in advance.

Cheers,

Ahmad

Re: Data Layout

MK_QSL — Fri, 31 Oct 2014 17:14:24 GMT

CrossTable(Type, Data)

Load

Date,

Fulltime,

Part-time,

Contractor

From TableName;

Re: Data Layout

Fri, 31 Oct 2014 17:17:48 GMT

Thank you for your response. can you clarify, so you're saying load it as a crosstable which i get. That would mean i need to create a table for all other projects, correct? Secondly, how will I be able to establish this information belongs to "Project1" for instance.

Re: Data Layout

MK_QSL — Fri, 31 Oct 2014 17:29:25 GMT

How many tables you have?

Re: Data Layout

Fri, 31 Oct 2014 17:31:57 GMT

4 other tables including this one. I have 5 projects in total.

Re: Data Layout

MK_QSL — Fri, 31 Oct 2014 17:46:49 GMT

Can you provide sample files along with your requirements?

Re: Data Layout

Fri, 31 Oct 2014 17:52:54 GMT

Attached, it's the exact same table I pasted up, just replicated 5 times (in different tabs) with their respective resource values.

Re: Data Layout

Mon, 03 Nov 2014 03:16:39 GMT

any ideas on this would be greatly appreciated.

Re: Data Layout

MK_QSL — Mon, 03 Nov 2014 10:35:16 GMT

Use below in script

============================================

FOR EACH i in 'A', 'B', 'C', 'D', 'E'

Test:

LOAD Date,

Fulltime,

[Part-time],

Contractor

FROM

[Project Resource.xlsx]

(ooxml, embedded labels, table is [Project $(i)]);

CrossTable(Type, Data)

Load * Resident Test;

Drop Table Test;

=============================================

Now create a Line Chart

Dimension

Date

Type

Expression
SUM(Data)

Re: Data Layout

Mon, 03 Nov 2014 13:41:13 GMT

can you explain what this part of the script is:

NEXT

CrossTable(Type, Data)

Load * Resident Test;

Drop Table Test;

I tried loading it but i get an error for almost every line. thank you

Re: Data Layout

MK_QSL — Mon, 03 Nov 2014 13:44:03 GMT

Provide your qvw !

Re: Data Layout

Mon, 03 Nov 2014 13:49:15 GMT

uploaded, thank you. please note i actually only started with 2 of the tables just to see it work.

Re: Data Layout

MK_QSL — Mon, 03 Nov 2014 14:20:09 GMT

You haven't used my full code....

Re: Data Layout

Mon, 03 Nov 2014 14:26:34 GMT

sorry, which part haven't i used? scroll to the bottom it's all there, no?

Re: Data Layout

MK_QSL — Mon, 03 Nov 2014 14:32:39 GMT

You used only below

CrossTable(Type, Data)

Load * Resident Test;

Drop Table Test;

NEXT if starting with FOR loop

FOR Each.....

CODE.

....

...

Re: Data Layout

Mon, 03 Nov 2014 14:38:03 GMT

Ahhh I see, so the

NEXT

CrossTable(Type, Data)

Load * Resident Test;

Drop Table Test;

goes after ever table load. Okay sounds good. But now I'm getting a "Script Error":

Script Line Error:

NEXT

Would you like to see qvw?

Re: Data Layout

MK_QSL — Mon, 03 Nov 2014 14:40:22 GMT

Sure

Re: Data Layout

Mon, 03 Nov 2014 14:44:48 GMT

uploaded

Re: Data Layout

Mon, 03 Nov 2014 15:29:32 GMT

any idea what i did incorrectly?

Re: Data Layout

Tue, 04 Nov 2014 13:44:52 GMT

Still looking for a solution to this, if anyone can shed some light I'd greatly appreciate it,