QlikView App Dev

Report Inappropriate Content · ‎2017-04-11

Currently I am using the below script to load multiple files from my local desktop and it's working fine:

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
[..\..\..\..\..\Desktop\JG_ALL\*.xlsx]
(ooxml, no labels, table is [Report Data 1])

where
C = 'Document'
and C <> 'Item Type'
and D <> 'MOSS-Prod-SearchSvc <GLOBAL\moss-prod-searchsvc>'
and D <> 'User Id'
and E <> 'Document Location'
and E LIKE '*sales/sales_central/SalesEssentialsAssets/*'
and not WildMatch(E,'sales/sales_central/SalesEssentialsAssets/Forms/*')
and F <> 'Occurred (GMT)'
and G <> 'Event'
and I <> 'Event Source'
;
STORE SP_ASSETS INTO $(vQVD)CBIC\SP_ASSETS.QVD(QVD);

Now,

With the same script I need to load same multiple files from SharePoint but I am unable to do so.

For example if my SharePoint site is http://abc.com/Reports

and the file names in the Reports folder are as follows:

Audit_Log_2017-03-01T080000

Audit_Log_2017-03-01T080000_(5)

Audit_Log_2017-03-01T080000_(10)

Audit_Log_2017-03-01T080000_(9)

Audit_Log_2017-03-01T080000_(8)

Audit_Log_2017-01-29T080002_(8)

Audit_Log_2017-01-29T080002

Audit_Log_2016-12-01T080000_(10)

Audit_Log_2016-12-01T080000_(2)

Audit_Log_2016-08-01T070000

Run_a_custom_report_2016-11-04T090238

Run_a_custom_report_2017-1-02T0142566

and so on...and there are files for year before 2016 in this folder and for each month there are almost 10 files ending in _(2) so on to _(10).

Log files are added to this folder every month and there are multiple files added as all data doesn't fit in one excel document.

So my question is how do I pull only the Audit_Log_* files for year 2016 and 2017 excluding all the Run_* files directly from sharepoint location and load into Qlikview.

hector_munoz · ‎2017-04-11

Hi Deb,

Coul you load at least one of them? In that case, could you share the script?

Regards,

H

Anil_Babu_Samineni · ‎2017-04-12

You can use where condition with wild match like for Share point link with qvd

WildMatch(FieldName, 'Audit_Log_*') and Match(Year, '2016','2017');

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Report Inappropriate Content · ‎2017-04-12

@Anil

Thanks for the reply.

Can you tell me where to incorporate that condition in the above script? I placed it where all my conditions are but it's still throwing the same error. . It's a Zip error.

What should be the from statement. For example my SharePoint Location for the files is http://abc.com/Reports and if I put from http://abc.com/Reports/*.xlsx it won't work.

So with your suggestion can you tell me what my from statement should be and where should I place your suggestion condition in the script?

@Munoz

Yes I can pull a single file directly from SharePoint. In that case you just have to put the direct SharePoint site in the FROM statement and rest of the script is the same. For example if you want to pull file named 'Audit_Log_2017-03-01T080000'

From statement should be like this:

FROM
[ http://abc.com/Reports/Audit_Log_2017-03-01T080000.xlsx]
(ooxml, no labels, table is [Report Data 1])

hector_munoz · ‎2017-04-12

Hi Deb,

Try:

LOAD *

FROM [http://abc.com/Reports/Audit_Log_20??-??-?????????.xlsx]

Regards,

H

Report Inappropriate Content · ‎2017-04-12

Hi Munoz,

Let's rethink this. Below is the script which works fine when I pull a single excel file from SharePoint. But the same doesn't work will I try to pull all the files with *.xlsx .

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
[http://abc.com/Reports/Audit_Log_2017-03-01T080000_(10).xlsx]
(ooxml, no labels, table is [Report Data 1])

where

C = 'Document'
and C <> 'Item Type'
and D <> 'MOSS-Prod-SearchSvc <GLOBAL\moss-prod-searchsvc>'
and D <> 'User Id'
and E <> 'Document Location'
and E LIKE '*sales/sales_central/SalesEssentialsAssets/*'
and not WildMatch(E,'sales/sales_central/SalesEssentialsAssets/Forms/*')
and F <> 'Occurred (GMT)'
and G <> 'Event'
and I <> 'Event Source'
;
STORE SP_ASSETS INTO $(vQVD)CBIC\SP_ASSETS_DIRECTFETCH.QVD(QVD);

In the above script how should your suggestion be incorporated. Can you resend the above script with your updates to pull all the excel files in the folder for year 2016 and 2017.

Anil_Babu_Samineni · ‎2017-04-12

All the file means? Do you have pagination over Sharepoint?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

hector_munoz · ‎2017-04-12

Try:

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
[http://abc.com/Reports/Audit_Log_20??-??-??T??????*.xlsx]
(ooxml, no labels, table is [Report Data 1])

where

C = 'Document'
and C <> 'Item Type'
and D <> 'MOSS-Prod-SearchSvc <GLOBAL\moss-prod-searchsvc>'
and D <> 'User Id'
and E <> 'Document Location'
and E LIKE '*sales/sales_central/SalesEssentialsAssets/*'
and not WildMatch(E,'sales/sales_central/SalesEssentialsAssets/Forms/*')
and F <> 'Occurred (GMT)'
and G <> 'Event'
and I <> 'Event Source'
;
STORE SP_ASSETS INTO $(vQVD)CBIC\SP_ASSETS_DIRECTFETCH.QVD(QVD);

Report Inappropriate Content · ‎2017-04-12

@ Anil,

All the files means I need to pull all the files in 'Reports' folder from the SharePoint path http://abc.com/Reports/

The files in the folder are named like below: