Determine the values [Cross Table]

[#1]

*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.

[Jason_Michaelides]

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

[#3]

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.

[amars]

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

[#5]

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

[#6]

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.

[amars]

Hi,

Please check the attached file containing one more alternative.

Thanks

Amar

[#8]

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

[#9]

bump

[#10]

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