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: 
renjithpl
Specialist
Specialist

How to use WHERE Clause when Cross Table is used

Hi all,

my table looks like below:

UserReview26-Aug27-Aug28-Aug18-SepTotal
LeninGreenberg_014280122
WilliamGreenberg_024669115
MercyGreenberg_034661107


i used cross table -

CrossTable
(Date, Data, 2)
LOAD
*
FROM
[Assignment Tracker.xls]
(biff, embedded labels, table is Production$) ;

but when loading, i am getting the field "Total" under Date Field. so I used Where clause,

Directory;

CrossTable
(Date, Data, 2)
LOAD
*
FROM
[Assignment Tracker.xls]
(biff, embedded labels, table is Production$)
where Date <> 'Total';

but its showing error.

My question is how to eliminate "Total" when loading, but i dont want to delete by (Delete Selected) method.

Please i appreciate your answer.

regards

Ren







1 Solution

Accepted Solutions
Not applicable

Hi,

If i understood your question, you can load you data with crooss table and delete your data by taking resident from previoulsy loaded cross table data.

You code should be as follow.

Directory;

TEST_TEMP:

CrossTable
(Date, Data, 2)
LOAD
*
FROM
[Assignment Tracker.xls]
(biff, embedded labels, table is Production$)
where Date <> 'Total';

NOCONCATENATE

TEST:

LOAD *

Resident

TEST_TEMP where Date <> 'Total';

Drop Table TEST_TEMP;

Hope this will help you.

- Sridhar

View solution in original post

7 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

The easiest way to know the syntax would be to generate a sample CROSSTABLE query by using Table wizard and go through the screen while selecting proper options for crosstable (under transformation data) steps. After wizard is complete, it will give you a sample query for your scenario.

Hope this helps.

renjithpl
Specialist
Specialist
Author

sorry, Rakesn, it will work that way, but if i delete like that, in future if i insert any columns in my excel sheet, it shows wrong data in qlikview.

Not applicable

Hi,

Can you attach the excel sheet with dummy data?

It should work or Might be if date columns data formats are date format whereas string you are putting as Text.

It will not allow you to put this filter.

Thanks,

Raj Kishor

Not applicable

hi,

CrossTable
(Date, Data, 2)
LOAD
*
FROM
[Assignment Tracker.xls]
(biff, embedded labels, table is Production$) ;

drop field Total from Production;

🙂

Miguel_Angel_Baeyens

Hello Ren,

You can always do a

CROSSTABLE (Date, Data, 2) LOAD Field1, Field2, Field3 FROM File.xls;


Although it means to hardcode every field you want to load from the original table.

Another work around is to do a resident load of all fields but the one you want to exclude after the crosstable has loaded. This may be easier, as you don't have to explicitly use the fields you want to load and you can use a where clause.

Probably the easier is to first load the table completely, then

DROP FIELDS Total, Total2;


and then do the Crosstable load.

Hope that helps

Not applicable

Hi,

If i understood your question, you can load you data with crooss table and delete your data by taking resident from previoulsy loaded cross table data.

You code should be as follow.

Directory;

TEST_TEMP:

CrossTable
(Date, Data, 2)
LOAD
*
FROM
[Assignment Tracker.xls]
(biff, embedded labels, table is Production$)
where Date <> 'Total';

NOCONCATENATE

TEST:

LOAD *

Resident

TEST_TEMP where Date <> 'Total';

Drop Table TEST_TEMP;

Hope this will help you.

- Sridhar

renjithpl
Specialist
Specialist
Author

Hi Sridhar it worked well, thanks a lot, you took away my big headache. thanks a lot.