Store in a Qvd

[moath]

I need to store the following script after the last step, but I couldn't can you help me please:

Step_1:

LOAD Service,
Month,
Price

FROM
[C:\Users\A\Desktop\A.xlsx]
(ooxml, embedded labels, table is Sheet1);

store Step_1 into [C:\Users\A\Desktop\Step_1.qvd](qvd);

Step_2:

LOAD
Distinct Service Resident Step_1;

Step_3:
Generic LOAD Service,Month,Price resident Step_1;

drop table Step_1;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Step_3.*') THEN

LEFT JOIN ([Step_2]) LOAD distinct * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];

ENDIF

NEXT i

[stevedark]

Hi @moath 

Not sure what you mean by "store the script". If you mean persist the data to QVD files, then these blog posts have you covered:

https://www.quickintelligence.co.uk/qlikview-qvd-files/
https://www.quickintelligence.co.uk/write-csv-qlikview-store/

I see in your script you are already saving Step_1 to QVD. You just need to do the same for Step_2 at the end, if you want the combined table, or if you want each of the Step_3 tables stored you will need to put this in the loop, using the variable name $(vTable)

Note that in Sense the way you refer to the output location is a little different, you need to use lib://DataFiles/MyQVD.qvd (qvd) rather than referring to a drive letter or UNC path.

Hope that helps.

Steve

[moath]

Hi @stevedark 

Thank you for your response!

Honestly, What I need is to save the results  after applying the new format which is already written in the script. So I can proceed with my new table by storing it and load it again.

Here is a quick example:

Original table:

Service	Month	Price
A	January	100
A	February	100
A	March	105
A	April	110
B	January	150
B	February	160
B	March	170
B	April	180

The new format:

Service\Month	January	February	March	April
A	100	100	105	110
B	150	160	170	180

Thank you in advance!

[eronevil]

Hello,

If you need to unpivot table use Generic load. Long table to wide table.

Generic ‒ Qlik Sense on Windows

The Generic Load - Qlik Community - 1473470

[stevedark · Accepted solution]

Hi @moath 

In that case, you are doing most of the work already, so you just need to duplicate the STORE statement you have for stage_1 and do it for stage_3 as the very last line of your script:

store Step_3 into [C:\Users\A\Desktop\FlatFormat.qvd] (qvd);

Or, if you want it in a format that can be read into other products:

store Step_3 into [C:\Users\A\Desktop\FlatFormat.csv] (txt);

As an aside, for analysis in Sense or QlikView, the long table with fewer columns is almost always better. You can create the flat table view in the front end by using a pivot table.

Steve

[moath]

Thanks @stevedark 

Where should I write store Step_3 into [C:\Users\A\Desktop\FlatFormat.qvd] (qvd); ?

in which line?

[stevedark · Accepted solution]

After NEXT i. You could put both STORE lines, and write files of both types out.

Steve

[moath]

Thanks Steve @stevedark  !  appreciate it !

[moath]

Thanks @eronevil  !

[stevedark]

No worries @moath . Thanks for marking the solutions - as this really helps other Community users.

Steve