topic Re: CrossTab large Dataset multiple columbs in QlikView

CrossTab large Dataset multiple columbs

ruzvmun — Sat, 16 Nov 2024 17:57:27 GMT

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.

Re: CrossTab large Dataset multiple columbs

Lisa_P — Sun, 18 Oct 2020 03:33:22 GMT

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);

Re: CrossTab large Dataset multiple columbs

ruzvmun — Sun, 18 Oct 2020 04:38:28 GMT

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 🙂

Re: CrossTab large Dataset multiple columbs

Vegar — Sun, 18 Oct 2020 05:58:32 GMT

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

Re: CrossTab large Dataset multiple columbs

ruzvmun — Sun, 18 Oct 2020 06:52:05 GMT

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

Re: CrossTab large Dataset multiple columbs

Vegar — Sun, 18 Oct 2020 08:04:16 GMT

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

Try wrapping [] around like this.

[ARRAY $(i) AGENT STATUS] as Status

Re: CrossTab large Dataset multiple columbs

ruzvmun — Mon, 19 Oct 2020 00:06:19 GMT

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 🙂

Re: CrossTab large Dataset multiple columbs

ruzvmun — Mon, 19 Oct 2020 00:09:52 GMT

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