CrossTab large Dataset multiple columbs

[ruzvmun]

Hi Guys continuously learning and seeing the Value of QV,

I just realized that CrossTable() could be a solution to my problem.

I would like to create a crosstable script that uses data from 1+ columns. The data resides in a large QVD file and I would like to select a sub-dataset based on a group of fields.

I have attached input data and output data. This one uses just the 2 highlighted columns but imagine there are more in that category. 

Can someone please assist me,

Please let me know if I need to clarify something.

Thank you for your help.

[Lisa_P · Accepted solution]

This is not really a crosstable scenario.  It is combining the data in another way using an outer join.

Here is the script I used to get the result.

LOAD Emp_ID,
Emp_Name,
ARRAY_06_Emp_LEVEL as Array_Level,
// ARRAY_07_Emp_LEVEL,
ARRAY_06_Emp_Code as Array_EmpCode
// ARRAY_07_Emp_Code
FROM
[C:\Users\axn\Documents\Community\Sample_CT_Data.xlsx]
(ooxml, embedded labels, table is Input_Data);

Join
LOAD Emp_ID,
Emp_Name,
// ARRAY_06_Emp_LEVEL,
ARRAY_07_Emp_LEVEL as Array_Level,
// ARRAY_06_Emp_Code,
ARRAY_07_Emp_Code as Array_EmpCode
FROM
[C:\Users\axn\Documents\Community\Sample_CT_Data.xlsx]
(ooxml, embedded labels, table is Input_Data);

[ruzvmun]

This is awesome,exacly what I need.

So do I have to repeat the queries for the remaining Array Subsets. I have up to 15.

I think it will work but wanted to know if there is a simpler way than that.

Thanks again 🙂

[Vegar]

To simplify try to loop your 15 iterations.
Something like this :

Emp:
LOAD Emp_ID, Emp_Name
FROM [Data];

For each i in '01','02','03',..., '14', '15'
Left join (Emp)
LOAD
Emp_ID,
Emp_Name,
ARRAY_$(i)_Emp_LEVEL as Array_Level,
ARRAY_$(i)_Emp_Code as Array_EmpCode
FROM [Data];
Next i;

You can of cource choose a normal for loop as well, but make sure that your i formats as '00'.

[ruzvmun]

Thanks Vegar, I am sure this will work. however I am not getting values for the loops. I think I am not scripting correctly somewhere.

I have attached a screenshot. Is there any reason the text turns black after the loop macro?

Arnold

[Vegar · Accepted solution]

I notice that you have white spaces in some of your field names.

Try wrapping [] around like this. 

[ARRAY $(i) AGENT STATUS]  as Status 

[ruzvmun]

It worked once I changed 'Outer Join' to 'Join'. This is probably because of the structure of my Data.

Will confirm this by validating CRM App..

This has been awesome, always  great to learn new things

Thank you Vegar and Lisa 🙂

[ruzvmun]

Now I need to create something similar to an 'Org Chart' using this data. Will do some research.