Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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.
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
hi,
CrossTable
(Date, Data, 2)
LOAD
*
FROM
[Assignment Tracker.xls]
(biff, embedded labels, table is Production$) ;
drop field Total from Production;
🙂
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
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
Hi Sridhar it worked well, thanks a lot, you took away my big headache. thanks a lot.