Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
julioarriaga
Creator II
Creator II

Why is 'Qualify' statement multiplying the total record and column number in a 'Crosstable' statement?

Hello everyone,

Why is a 'Qualify' statement adding an extra column (tablename.tablename.date) multypling my total row number and altering the records' names in a 'Crosstable' statement?

Thanks in advance.

[Incorrect]

Input:

  1. Qualify *; 
  2. Cross: 
  3. CrossTable(Country, Sales) 
  4. LOAD * INLINE [  0 as Input
  5. Date, USA, UK, Japan, Germany
  6. 2013-01-01, 123, 100, 80, 40
  7. 2013-02-01, 134, 111, 75, 50
  8. 2013-03-01, 155, 95, 70, 60
  9. 2013-04-01, 165, 85, 88, 50
  10. 2013-05-01, 154, 125, 90, 70
  11. 2013-06-01, 133, 110, 75, 99
  12. ];

Output:

Evidence1.PNG

[Correct]

Input:

  1. Unqualify *; 
  2. Cross: 
  3. CrossTable(Country, Sales) 
  4. LOAD * INLINE [  0 as Input
  5. Date, USA, UK, Japan, Germany
  6. 2013-01-01, 123, 100, 80, 40
  7. 2013-02-01, 134, 111, 75, 50
  8. 2013-03-01, 155, 95, 70, 60
  9. 2013-04-01, 165, 85, 88, 50
  10. 2013-05-01, 154, 125, 90, 70
  11. 2013-06-01, 133, 110, 75, 99
  12. ];

Output:

Evidence1.PNG

1 Solution

Accepted Solutions
marcus_sommer

I must admit that I had never tried to combine qualify and crosstable and I think it's really inappropriate to each other. I assume that behind this feature is a kind of loop which concats everything together and that your noticed behaviour is caused from that. In general you shouldn't apply any transformations on crosstable and doing it afterwards within a resident-load (lesser problems and I assume a better performance will follow).

- Marcus

View solution in original post

3 Replies
marcus_sommer

Qualify is like a very strong poison. Carefully used by certain situations it could be useful but applying to (nearly) each load is the opposite of it and could create a varity of problems ...

Beside this I would adjust it in this way:

  1. Unqualify *; 
  2. Cross: 
  3. CrossTable(Country, Sales, 1
  4. LOAD * INLINE [  0 as Input
  5. Date, USA, UK, Japan, Germany
  6. 2013-01-01, 123, 100, 80, 40
  7. 2013-02-01, 134, 111, 75, 50
  8. 2013-03-01, 155, 95, 70, 60
  9. 2013-04-01, 165, 85, 88, 50
  10. 2013-05-01, 154, 125, 90, 70
  11. 2013-06-01, 133, 110, 75, 99
  12. ];

- Marcus

julioarriaga
Creator II
Creator II
Author

Thanks, I added that Unqualify statement just to make it clear what was being done. Nevertheless, do you know why the Qualify statement does that? Does that mean that Qualify is simply inappropiate for the statement Crosstable?

marcus_sommer

I must admit that I had never tried to combine qualify and crosstable and I think it's really inappropriate to each other. I assume that behind this feature is a kind of loop which concats everything together and that your noticed behaviour is caused from that. In general you shouldn't apply any transformations on crosstable and doing it afterwards within a resident-load (lesser problems and I assume a better performance will follow).

- Marcus