topic Export by Dimension in QlikView

Export by Dimension

Mon, 09 May 2011 18:00:57 GMT

When dealing with large data structures, it seems like an 'Export' option is really only viable if the dataset is within the limitations of the format it is exporting to.. for example, newer version of Excel only support up to 1.1 million rows, and previous versions were only compatible up to 65k or so (memory ceilings I assume).

That said, it seems like a great solution would be the ability to auto-export a table to a format automatically in the script itself.

If I have a pre-built QVD that I want to divide into 3 separate exports (let's just say for argument's sake that it's by 'DIM1' dimension - or a specific site/location), what would be the method to have QlikView script do such a thing? How do you script a loop to only export the values in the 'current' dimension it is on until it has divded up the table into each respective individual dimension tables?

Export by Dimension

erichshiino — Mon, 09 May 2011 19:02:50 GMT

Hi,

If you check my post at this thread:

http://community.qlik.com/message/113679#113679

There is a sample application to make selections on a field one by one and export a chart to word.

Inside the For-Next structure you could include a different export method according to your need.

Tell if you need some help to understand or adapt it.

Regards,

Erich

Export by Dimension

rbecher — Mon, 09 May 2011 19:06:14 GMT

Hi,

I presume that you want to use STORE to a TXT (csv) file in the script. The only way I see is to load each part you want to export to a second table with a filter where clause:

temp:

LOAD *

FROM xyz.qvd (qvd)

Where dimension = 'DIM1';

STORE temp INTO dim1export.txt (txt);

DROP Table temp;

// repeat for DIM2 to DIM3, you could also have a loop here

- Ralf

Export by Dimension

Mon, 09 May 2011 19:27:24 GMT

So, a combination of what you BOTH said seems more appropriate. Part of that has to do with my explanation of the issue, and expected output I'm looking for:

1. Erich - Your FOR loop looks like it checks for all possible values in a given field, and moves through them until all of the possible field values have been dealt with. This portion is exactly the kind of behavior I was looking to do...

2. Ralf - You actually had the exact output and concept I was looking for (where Erich was doing more of an immediate export to Word or similar app).

Now, I need to figure out a way to combine the two concepts... I think with this I should be able to collectively put it together. If I come up with an exact step-by-step walkthrough, I will most certainly come back and post it.

Just going to take some time, tweaking, and a little more referencing of the scripting language capabilities.

Export by Dimension

erichshiino — Mon, 09 May 2011 19:33:20 GMT

Glad it helped!

If you want to make a loop during script, the syntax will be a bit different.

You can get an example here:

http://community.qlik.com/message/60269#60269

Export by Dimension

Mon, 09 May 2011 20:12:52 GMT

Nope, I'm actually stuck...

So the biggest issue here is that we need to be able to implement the variables in the filenames - in otherwords, as it is pushing through the different possible dimensions (say integers 1-3 are the values possible), it should add that as a portion of the filename.

i.e. :

1. Look at QVD, and for all rows where site_id = 1, save to SITE_INVENTORY_1.CSV

2. (loop assumed) Look at QVD, and for all rows where site_id = 2, save to SITE_INVENTORY_2.CSV

... the syntax that you referenced in the other post Erich didn't seem to apply easily to what I was trying to do because of this small detail.

Re: Export by Dimension

erichshiino — Mon, 09 May 2011 20:36:08 GMT

No, problem.

Please, check attachment.

Export by Dimension

rbecher — Mon, 09 May 2011 20:39:05 GMT

As I wrote, now with loop and your file and field names:

for a=1 to 3

temp:

LOAD *

FROM xyz.qvd (qvd)

Where site_id = $(a);

STORE temp INTO SITE_INVENTORY_$(a).csv (txt);

DROP Table temp;

next;

- Ralf

Re: Export by Dimension

Mon, 09 May 2011 20:50:54 GMT

Why does my version of QlikView not believe that "NoConcatenate" should be allowed to go where it is in your example?

Re: Export by Dimension

erichshiino — Mon, 09 May 2011 20:52:13 GMT

The syntax checker in QV 10 doesnot recognize some script code and even expression code when you create some complicated set analysis...

Re: Export by Dimension

Mon, 09 May 2011 20:54:01 GMT

Ralf - What about a more dynamic approach? Let's say we've only got 3 sites/stores right now, but that we want the script to check for possible values first before assuming it's just '1 through 3'?

You are both widening my knowledge by leaps and bounds...

Re: Export by Dimension

Mon, 09 May 2011 20:56:25 GMT

Erich - Your answer was actually right on the money.. I just ignored the syntax error and let it roll, and it created each file just as it was expected to!

That was fantastic, thank you!

Export by Dimension

rbecher — Mon, 09 May 2011 21:01:37 GMT

I just made it simple as possible so that's more understandable. It's always easy to complicate things..

Btw. the NoConcatenate is not needed in this case because the table gets dropped. If needed the right syntax is:

tempTable:

NoConcatenate load * resident Table

where site_id = $(vTable);

- Ralf

Export by Dimension

Mon, 09 May 2011 21:04:50 GMT

Gotcha - I see your point now also about it being dropped

I'm still fresh eyes when it comes to QV, but this community is so fantastic and helpful!

Thanks again fellas!