topic Re: Determine the values [Cross Table] in QlikView

Determine the values [Cross Table]

Thu, 25 Oct 2012 06:11:43 GMT

*Edited*

Hello and Good Day!

Since I am new at using qlikview, I find it hard to analyze what should be my steps when cleaning an excel file. I have

an idea of using crosstables when encountering this kinds of problems.

attached file is the excel file.

What I need:

1. To know if it is under what year.

2. To know if it is under Revenue or Visitors

3. To know what is public or private

Thank you.

Re: Determine the values [Cross Table]

Jason_Michaelides — Thu, 25 Oct 2012 06:34:43 GMT

In order to get the best help in this forum please post as much detail as you can. In this case, what is the script you are usin to generate the CrossTable? And can you provide an example of what your source data is and what you want to end up with?

Jason

Re: Determine the values [Cross Table]

Thu, 25 Oct 2012 06:49:09 GMT

Hello, thank you for your reply.

I will provide you a sample xls file.

As you can see, I want to determine that the values listed is for Public or for Private.

Re: Determine the values [Cross Table]

amars — Thu, 25 Oct 2012 06:49:46 GMT

Hi donatzz,

The CrossTable won't help in this case as you have two headers in this case

1. Private/ Public

2. Value1 / Value1a

I know this is not the most efficient way of doing things but can you write it this way.

1. Read only the First Column

Table1:

Load Fields,

'Private' As Type,

'Value 1' As SubType,

Value1 As Data

From ...... Xls.

2. Read the next column

Concatenate (Table1)

Load Fields,

'Private' As Type,

'Value 1a' As SubType,

Value1a As Data

From ...... Xls.

3.Follow the Same way for the rest of the two columns.

See if this approach helps.

Thanks

Amar

Re: Determine the values [Cross Table]

Thu, 25 Oct 2012 06:52:22 GMT

I want my data to end up as:

1. To filter out which is Public or Private

2. Also determine that it is under Revenue or Visitors

that's it for now

Re: Determine the values [Cross Table]

Thu, 25 Oct 2012 06:55:02 GMT

I will try this one, thank you.

Although I have to tell you that the columns that I will be loading is 157columns,

what I have just stated in the example is only 2 columns and that is why I have decided that Cross Table might be the best approach since the transformation of tables will be one bloody work.

Re: Determine the values [Cross Table]

amars — Thu, 25 Oct 2012 07:09:51 GMT

Hi,

Please check the attached file containing one more alternative.

Thanks

Amar

Re: Determine the values [Cross Table]

Thu, 25 Oct 2012 08:26:34 GMT

I have seen your point, I am still a little bit confused on what to be done. I want to 'recreate' the excel file that I have imported even with the cross tables transformations. Is it possible to be done on what you have created?

I have found a solution on how to determine if it is public or private, now my problem is how to know if it is a Revenue or the Visiting Record.

Thank you sir for your help. +1

Re: Determine the values [Cross Table]

Sun, 28 Oct 2012 13:23:14 GMT

bump

Re: Determine the values [Cross Table]

Sun, 28 Oct 2012 13:57:00 GMT

whaen u say u've 157 cols,under what heading are those, are they category like Revenue/Visitors or Public/Private or Value1/Value2 ?

Regards

Re: Determine the values [Cross Table]

Gysbert_Wassenaar — Sun, 28 Oct 2012 18:41:54 GMT

See attached files. This is rather convoluted. Perhaps somebody has a more efficient solution. However, I think fixing your excel sheet so it's more easily imported is a better solution. I hope this helps.

Re: Determine the values [Cross Table]

Mon, 29 Oct 2012 00:32:16 GMT

based on the excel file, they are categorized based on revenue and visitors, and it depends on the year.

so based on the sample excel file I have given, those two category Revenue and Visitors is under for example September 2011, another one is under Semptember 2012 but under the same category [Revenue and Visitors]

Re: Determine the values [Cross Table]

Mon, 29 Oct 2012 00:55:12 GMT

thank you

I will try your solution!

Re: Determine the values [Cross Table]

Mon, 29 Oct 2012 02:57:19 GMT

bump. I have updated my post. Please see attached file.

Re: Determine the values [Cross Table]

Mon, 24 Dec 2012 20:14:02 GMT

Did you ever find a simpler solution to loading multiple headers into a crosstable? I don't really understand what your script is doing...

Re: Determine the values [Cross Table]

Wed, 06 Jan 2016 06:39:49 GMT

Gysbert says it's convoluted. But I think Gysbert's load script just manually provides the "members" of each Dimension -- hard-coded like "Public" / "Private" , "Revenue" / "Visitors". The challenge for me is there is it can't "scale" to arbitrary number of Members and Dimensions unless we hard-code it.

So I think Gysbert is correct -- best thing is to "fix the Excel sheet"

Here is a question on SuperUser who wants to do the same, that is "unpivot" a 1-Dimension Excel file.

Is it possible to "unpivot" or "reverse pivot" in Excel? - Super User

I asked in the comments how to do 2-Dimensions Excel file, or arbitrary number of Dimensions, which I think is possible

http://superuser.com/questions/78439/is-it-possible-to-unpivot-or-reverse-pivot-in-excel/78464#comment-1420947

Wonder if anyone has advice on "fixing the Excel sheet"