STORE function help Require

[pra_kale]

Hi,

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

1. I want to know how i can save files for different channels in .csv format. Means Banka.csv,  Agency.csv...etc.
2. 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.

A:

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.

[marcus_sommer]

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

[Kushal_Chawda]

To load channel wise CSV you can do something like below

A:

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;

[pra_kale]

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.

A:

LOAD Policy_no,

     Premium,

     Year,

     Month,

     Channel

FROM

(ooxml, embedded labels, table is Sheet1);

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

NoConcatenate

B:

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);

NEXT

Drop Table B;

[Kushal_Chawda]

A:

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;

[pra_kale]

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.

A:

LOAD Policy_no,

     Premium,

     Year,

     Month,

     Channel

FROM

(ooxml, embedded labels, table is Sheet1);

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

NoConcatenate

B:

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);

NEXT

Drop Table B;

STORE function help Require

[maxgro]

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

A:

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

B:

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;

[Kushal_Chawda]

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

A:

LOAD Policy_no,

     Premium,

     Year,

     Month,

     Channel

FROM

(ooxml, embedded labels, table is Sheet1);

B:

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;

[maxgro]

yes, thanks

[HirisH_V7]

Hi ,

Do check this,

Storing all tables in Qlikview file to QVD

-Hirish