topic Re: Send data to Excel preserving Table View in QlikView

Send data to Excel preserving Table View

Fri, 27 Dec 2013 12:22:44 GMT

How can i, literaly, copy one QLikView sheet, say, to Excel, and load it back in QLikView on a blank sheet, while preserving the logic in the Table View ?

What i am doing from now is sending to Excel all tables from original document one by one, and loading them back using the Load wizard in script editor. All field are loaded into separate List Tables, and I try to build back the tables as in the original document. But as Table View is not at all replicated as in the original document, the tables i obtain with right click -> new sheet object are far from the original ones.

Do you have an idea?

Regards

Re: Send data to Excel preserving Table View

Gysbert_Wassenaar — Fri, 27 Dec 2013 13:33:40 GMT

You can't. What you see is the result of the way Qlikview works. You see all values from a field that exists in two tables. But not all values have records in both tables. So you will see empty fields for some records if you add the fields to a table box. This can be resolved by using a left keep or left join in the load script:

Table1:

LOAD A, B, C from abc.xls (biff, embedded labels, table is Sheet1$);

LEFT KEEP

Table2:

LOAD A, D, E from ade.xls (biff, embedded labels, table is Sheet1$);

Table2 will now only contain records where A also exists in Table1.

If you create a table box with fields A,B and C you won't see empty values for B and C. But if Table1 contains values of A that do not exist in Table2 then a table box with fields A,D and E will show all values of A including those that only exist in Table1 and will show empty values in field D and E for those values of A that do not exist in Table2.

Re: Send data to Excel preserving Table View

Fri, 27 Dec 2013 14:00:19 GMT

Thanks a lot, almost there.

Now script looks like this

TableA1:

LOAD A from abc.xls (biff, embedded labels, table is Sheet1$);

LEFT KEEP

TableA2:

LOAD A, Rev1, Rev2 from ade.xls (biff, embedded labels, table is Sheet1$);

TableB1:

LOAD B from abc.xls (biff, embedded labels, table is Sheet1$);

LEFT KEEP

TableB2:

LOAD B, Rev1, Rev2 from ade.xls (biff, embedded labels, table is Sheet1$);

With same script WITHOUT the LEFT KEEP keywords: if i create a new table box with fields A, Rev1, Rev2, i see rows where A has no value, but Rev1 and Rev2 have. this is because QLik does some implicit join between table B2 and table A2.

With same script WITH the LEFT KEEP keywords: i expect that now table box only contains box where A has non-empty value. is not the case. i have exactly same result as with the LEFT KEEP keywords.

Do you have an idea ? what is wrong in my script ?

Thanks

Re: Send data to Excel preserving Table View

Gysbert_Wassenaar — Fri, 27 Dec 2013 14:37:55 GMT

That could be because TableB2 also has Rev1 and Rev2 values. TableB2 has two fields in common with TableA2 so it is linked to TableA2. If you add the fields Rev1 and Rev2 you get all the values of Rev1 and Rev2 from both TableA2 and TableB2. The values from TableB2 don't necessarily have a value A associated with them so you would see empty cells for those values of Rev1 and Rev2.