Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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







Tags (2)
1 Solution

Accepted Solutions
Highlighted
Not applicable

How to use WHERE Clause when Cross Table is used

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
Highlighted
Partner
Partner

How to use WHERE Clause when Cross Table is used

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.

Highlighted
Specialist
Specialist

How to use WHERE Clause when Cross Table is used

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.

Highlighted
Not applicable

How to use WHERE Clause when Cross Table is used

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

Highlighted
Not applicable

How to use WHERE Clause when Cross Table is used

hi,

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

drop field Total from Production;

🙂

Highlighted

How to use WHERE Clause when Cross Table is used

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

Highlighted
Not applicable

How to use WHERE Clause when Cross Table is used

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

Highlighted
Specialist
Specialist

How to use WHERE Clause when Cross Table is used

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