topic Re: Scripting - Load Data According to given input file data in QlikView

Scripting - Load Data According to given input file data

kakani87 — Fri, 27 Jul 2018 13:23:02 GMT

Dear Folks ....

I would like to know the approach for achievement of a requirement.

Am having a sale table with me followed by Invoice number in that

Data should be loaded according to the input given by user

lets say with in all the invoice numbers available i want to load for only inv_no = 01,02,10,30 records from database while loading, if user gives 02,23,35 ... then consider as given from a file given by user.

Here I/p file may be .txt or .csv.

The way i know to load such kind of data using a where condition, but all the time this approach is not applicable when the count is higher in No of input values.

Hope someone can help me out of this.

Regards,

Kakani.

stalwar1‌

mbaeyens‌

tresesco‌

Re: Scripting - Load Data According to given input file data

BalaBhaskar_Qlik — Fri, 27 Jul 2018 14:56:25 GMT

May be like this:

If you want to enter value for region dynamically by user input. Try like this

Let vInvoiceNo = Input('Enter Invoice', 'Input box');

InvoiceInfo:

Load * from yourtable Where Invoice = '$(vInvoiceNo)';

While reloading, its ask user to enter Invoice Number. And it display those Invoice Number information alone.

Re: Scripting - Load Data According to given input file data

kakani87 — Fri, 27 Jul 2018 15:16:07 GMT

You mean to say dynamic filter using input box with assigning a variable to that ?

If so,let me explain about that i want to load only particular records based on the file provided by user.

lets say we have N no of transactions and among them user wants few transactions of data only to be displayed while loading/Fetching from database based on the user list of transactions to consider.

Can we load only the invoice no's from a file given.

Re: Scripting - Load Data According to given input file data

BalaBhaskar_Qlik — Fri, 27 Jul 2018 15:25:28 GMT

May be You can load all the data, but create a temp table to exclude those UN-necessary records.

Re: Scripting - Load Data According to given input file data

settu_periasamy — Fri, 27 Jul 2018 15:27:03 GMT

Hi,

May be check this ..

Create one variable for providing Input values. Like

vInput_Invoice

In the script,

Let vReplace_Invoice=Replace(vInput_Invoice,',','|');

Temp_Input:
LOAD Subfield('$(vReplace_Invoice)' ,'|') as Inv_Number AutoGenerate 1;

Sales:
LOAD * Where Exists(Inv_Number);

DROP Table Temp_Input;

Re: Scripting - Load Data According to given input file data

olivierrobin — Fri, 27 Jul 2018 15:37:34 GMT

hello

and what about this scenario ?

load InvoiceNumber from UserFile

load ..... from DataFile where exists(InvoiceNumber)

where UserFIle is the file given by the user and containing the desired InvoiceNumbers

Re: Scripting - Load Data According to given input file data

kakani87 — Sun, 29 Jul 2018 15:19:22 GMT

Thanks a bunch Robin.

This worked perfectly.

Posting A sample to help other people like me.

User_ip:

LOAD Invoice

FROM

..\include\InPut_data.xlsx

(ooxml, embedded labels, table is Sheet1);

sales:

LOAD Invoice,

TEXT(Branch) AS Branch,

Qty,

Value

FROM

..\include\Sale_sample.xlsx

(ooxml, embedded labels, table is Sheet1)

Where Exists(Invoice)

;