topic Re: STORE function help Require in QlikView

STORE function help Require

pra_kale — Fri, 01 Jan 2016 12:28:59 GMT

Hi,

I am attaching one sample file in that I have a channel column which contains the different different channels.

I want to know how i can save files for different channels in .csv format. Means Banka.csv, Agency.csv...etc.
How i can save data in Three parts..means Records 1 To 5 in one file...From 6 To 10 in second file and 11 To 15 in one file.

By below given way I can save entire file into .csv format.

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

(ooxml, embedded labels, table is Sheet1);

STORE A into E:\Qlikview\Sample_1.csv (txt);

But I don't know how to divide files based on condition in a script. Require help to resolve the issue.

Thanks in advance.

Re: STORE function help Require

marcus_sommer — Fri, 01 Jan 2016 13:19:50 GMT

You need to run your load within a loop which determind each content with an appropriate where-clause and store then the data-slices, see here an example with qvd's but the logic for you is the same (unless a for each loop - see help might easier to handle for a few string-filters):

Split Data YearWise QVDs

- Marcus

Re: STORE function help Require

Kushal_Chawda — Fri, 01 Jan 2016 14:12:48 GMT

To load channel wise CSV you can do something like below

LOAD distinct Channel

FROM

(ooxml, embedded labels, table is Sheet1);

for i=1 to fieldvaluecount('Channel');

let vChannel = fieldvalue('Channel',$(i));

$(vChannel):

LOAD Policy_no,

Premium,

Year,

Month,

'$(vChannel)' as Channel

FROM

(ooxml, embedded labels, table is Sheet1);

STORE $(vChannel) into E:\Qlikview\$(vChannel).csv (txt);

Drop Table $(vChannel);

NEXT

Drop Table A;

Re: STORE function help Require

pra_kale — Sat, 02 Jan 2016 06:31:15 GMT

Thanks Khush the given script is working fine.

But, I want my original data in the table to be intact. Because after executing the query I am losing my data from Qlik table.

To avoid this I am doing something like this but it is also not working as the script is giving error. I am able to keep the data in the table intact but second part i.e. saving data into .csv file is failed.

Can you please help in this.

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

(ooxml, embedded labels, table is Sheet1);

//STORE A into E:\Qlikview\Sample_1.csv (txt);

NoConcatenate

LOAD distinct Channel

Resident A;

for i=1 to fieldvaluecount('Channel');

let vChannel = fieldvalue('Channel',$(i));

$(vChannel):

LOAD Policy_no,

Premium,

Year,

Month,

'$(vChannel)' as Channel

Resident A;

STORE $(vChannel) into E:\Qlikview\$(vChannel).csv (txt);

Drop Table $(vChannel);

Drop Table B;

Re: STORE function help Require

Kushal_Chawda — Sat, 02 Jan 2016 06:43:22 GMT

LOAD distinct Channel

FROM

(ooxml, embedded labels, table is Sheet1);

for i=1 to fieldvaluecount('Channel');

let vChannel = fieldvalue('Channel',$(i));

$(vChannel):

LOAD Policy_no,

Premium,

Year,

Month,

'$(vChannel)' as Channel

FROM

(ooxml, embedded labels, table is Sheet1)

where Channel = '$(vChannel)' ;

STORE $(vChannel) into E:\Qlikview\$(vChannel).csv (txt);

Drop Table $(vChannel);

NEXT

Drop Table A;

Re: STORE function help Require

pra_kale — Sat, 02 Jan 2016 07:24:27 GMT

Hi,

The above script you have provided earlier as well..this is working fine.

But, I want my original data in the table to be intact. Because after executing the query I am losing my data from Qlik table.

To avoid this I am doing something like this but it is also not working as the script is giving error. By this I am able to keep the data in the table intact but second part i.e. saving data into .csv file is failed.

Can you please help in this.

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

(ooxml, embedded labels, table is Sheet1);

//STORE A into E:\Qlikview\Sample_1.csv (txt);

NoConcatenate

LOAD distinct Channel

Resident A;

for i=1 to fieldvaluecount('Channel');

let vChannel = fieldvalue('Channel',$(i));

$(vChannel):

LOAD Policy_no,

Premium,

Year,

Month,

'$(vChannel)' as Channel

Resident A;

STORE $(vChannel) into E:\Qlikview\$(vChannel).csv (txt);

Drop Table $(vChannel);

Drop Table B;

STORE function help Require

Re: STORE function help Require

maxgro — Sat, 02 Jan 2016 08:24:07 GMT

this works for me, just a small change in bold (also I changed the load and store path to test on my pc)

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

[sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

//STORE A into E:\Qlikview\Sample_1.csv (txt);

//NoConcatenate

LOAD distinct Channel Resident A;

for i=1 to fieldvaluecount('Channel');

let vChannel = fieldvalue('Channel',$(i));

$(vChannel):

NoConcatenate

LOAD Policy_no,

Premium,

Year,

Month,

Channel

Resident A

where Channel = '$(vChannel)' // added after Kush suggestion

;

STORE $(vChannel) into $(vChannel).csv (txt);

Drop Table $(vChannel);

NEXT

Drop Table B;

Re: STORE function help Require

Kushal_Chawda — Sat, 02 Jan 2016 09:13:04 GMT

You need to apply where clause also to filter the particular channel data

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD distinct Channel

Resident A;

for i=1 to fieldvaluecount('Channel');

let vChannel = fieldvalue('Channel',$(i));

$(vChannel):

noconcatenate

LOAD Policy_no,

Premium,

Year,

Month,

'$(vChannel)' as Channel

Resident A

where Channel= '$(vChannel)' ;

STORE $(vChannel) into E:\Qlikview\$(vChannel).csv (txt);

Drop Table $(vChannel);

NEXT

Drop Tables B;

Re: STORE function help Require

maxgro — Sat, 02 Jan 2016 11:17:56 GMT

yes, thanks

Re: STORE function help Require

HirisH_V7 — Sat, 02 Jan 2016 13:02:02 GMT

Hi ,

Do check this,

Storing all tables in Qlikview file to QVD

-Hirish

Re: STORE function help Require

pra_kale — Sat, 02 Jan 2016 13:02:08 GMT

"Thanks a ton"...Kush. It is now working fine.

Re: STORE function help Require

Kushal_Chawda — Sat, 02 Jan 2016 13:07:35 GMT

welcome

Re: STORE function help Require

Kushal_Chawda — Sat, 02 Jan 2016 13:08:00 GMT

welcome

Re: STORE function help Require

pra_kale — Sun, 03 Jan 2016 06:36:45 GMT

Hi Kush,

Just want to ask one more thing related to this..

If I want to add one more filter condition into this means with Channel if I want Year also, then how I can achieve this.

For e.g. I want, file to be get saved for 1 for Banka channel for Year 2010 and another file for Banka channel but for 2011 Year. In above script where I should make the changes.

What I did is, I concatenated Year and Channel columns and created a Key and that Key I have replaced with (VChannel) but this method is not working...it is giving error 'unknown statement 2010Agency'

LOAD Policy_no,

Premium,

Year,

Month,

Channel,

Year&Channel as Key

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD distinct Key

Resident A;

for i=1 to fieldvaluecount('Key');

let vKey = fieldvalue('Key',$(i));

$(vKey):

NoConcatenate

LOAD Policy_no,

Premium,

Year,

Month,

Channel,

'$(vKey)' as Key

Resident A

where Channel= '$(vKey)' ;

STORE $(vKey) into E:\Qlikview\$(vKey).csv (txt);

Drop Table $(vKey);

Drop Table B;

Re: STORE function help Require

Kushal_Chawda — Sun, 03 Jan 2016 14:13:11 GMT

create the another inner loop to achieve the same. see the below code

LOAD Policy_no,

Premium,

Year,

Month,

Channel

FROM

(ooxml, embedded labels, table is Sheet1);

for i=1 to FieldValueCount('Channel')

let vChannel = FieldValue('Channel',$(i));

New:

NoConcatenate

LOAD '$(vChannel)' as Channel,

Policy_no,

Premium,

Year as YEAR,

Month

Resident A

where Channel='$(vChannel)';

for j=1 to FieldValueCount('YEAR')

let vYear = FieldValue('YEAR',$(j));

Final:

NoConcatenate

LOAD *

Resident New

where YEAR='$(vYear)';

STORE Final into E:\Qlikview\$(vChannel)_$(vYear).csv (txt);

DROP Table Final;

NEXT j

DROP Table New;

NEXT i

Re: STORE function help Require

pra_kale — Sun, 03 Jan 2016 14:42:58 GMT

Kush...that's Great...

Thank You Very Much !!!

Re: STORE function help Require

Kushal_Chawda — Sun, 03 Jan 2016 14:50:36 GMT

Welcome bro