Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
keithcurley
New Contributor II

Dynamic assignment in load script

Dear All,

I want to assign a field value in a table during load, based on a value in a previously loaded table, i.e. Check_Table has a list of 20 Orders, Main_Table has all Orders, I want to mark each Order in Main_Table with a flag to identify if it is present in the Check_Table list or not.

could do a join of the two tables but this does not allow me to subsequently select Orders which are not in the Check_Table (as they will have a NULL value in the flag field and not selectable in my dashboard).

Hence, during my load script for Main_Table I want to use something along the lines of:

If [Order] in Check_Table,

     Flag assigned 'Yes'

Else

     Flag assigned 'No'

Does anyone know how I can do this in Qlikview?

Thanks in advance,

Keith

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Dynamic assignment in load script

Sorry an update in the script.

Use below.

CHECK:

LOAD

     ID,

     ORDERID AS CheckOrderID,

     'Yes' AS Flag

FROM

[..\Desktop\Checking.xlsx]

(ooxml, embedded labels, table is CHECK);

TEMP:

MAPPING LOAD CheckOrderID,

     Flag

Resident CHECK;

MAIN:

LOAD *,

     ApplyMap('TEMP', ORDERID, 'No') AS FlagExists;

LOAD Name,

     ORDERID

FROM

[..\Desktop\Checking.xlsx]

(ooxml, embedded labels, table is MAIN);

6 Replies
vishsaggi
Esteemed Contributor III

Re: Dynamic assignment in load script

Try this and let me know if not please share you main and check tables for a quick look at it.

Please find attached files and let me know how it works ?

vishsaggi
Esteemed Contributor III

Re: Dynamic assignment in load script

Sorry an update in the script.

Use below.

CHECK:

LOAD

     ID,

     ORDERID AS CheckOrderID,

     'Yes' AS Flag

FROM

[..\Desktop\Checking.xlsx]

(ooxml, embedded labels, table is CHECK);

TEMP:

MAPPING LOAD CheckOrderID,

     Flag

Resident CHECK;

MAIN:

LOAD *,

     ApplyMap('TEMP', ORDERID, 'No') AS FlagExists;

LOAD Name,

     ORDERID

FROM

[..\Desktop\Checking.xlsx]

(ooxml, embedded labels, table is MAIN);

MVP
MVP

Re: Dynamic assignment in load script

Check_Table:

load * inline [

Order

1

2

3

4

];

Main_Table:

load

  rowno() as Order,

  'another field' as field1

AutoGenerate 20;

Left Join (Main_Table)

LOAD

  Order,

  1 as FlagAssigned

Resident Check_Table;

keithcurley
New Contributor II

Re: Dynamic assignment in load script

Thanks for this Vish, worked perfectly!

I hadn't used ApplyMap much previously, might use it more in the future now...

Regards,

Keith

keithcurley
New Contributor II

Re: Dynamic assignment in load script

Hi Massimo,

Really appreciate the response, thanks for taking the time.

Once I got my head around ApplyMap though I found it easier to use Vish's approach this time.

Thanks,

Keith

MVP
MVP

Re: Dynamic assignment in load script

Me too I prefer the applymap solution. My answer was just to show a different solution.

Community Browser