How to use WHERE Clause when Cross Table is used

[renjithpl]

Hi all,

my table looks like below:

User	Review	26-Aug	27-Aug	28-Aug	18-Sep	Total
Lenin	Greenberg_01	42	80			122
William	Greenberg_02	46	69			115
Mercy	Greenberg_03	46	61			107

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

[disqr_rm]

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]

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.

[#4]

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

[#5]

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

[#7 · Accepted solution]

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]

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