topic Re: Create TABLE with the GENERIC LOAD in QlikView

Create TABLE with the GENERIC LOAD

ab92 — Tue, 02 Jan 2018 08:42:26 GMT

Good morning Everyone,

I wish you the best for this year !

After to test this tuto : Use cases for Generic Load | Qlikview Cookbook

I understand the function of the Generic Load thanks Cookbook for this help ^^, however I would like to create specific table (join some table after the Generic Load) .

For a specific need I want to join two tables in my application, but with this post actually propose to join all the tables.

Find below the script :

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

LET vTable=TableName($(i));

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

LEFT JOIN (OrdersTable) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

But I tried my best to improve this script but with my skills in Qlikview it's complicate, please someone can help me ?

I would like to join in this case only two table from the link Cookbook example :

Many thanks for your help,

Axel

Re: Create TABLE with the GENERIC LOAD

pradosh_thakur — Tue, 02 Jan 2018 09:27:59 GMT

Try this

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

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.Status_Shipped','Flags.Status_Returned') THEN

LEFT JOIN (OrdersTable) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

inside the wild match put the table name you want to join others will be as it is ..

Re: Create TABLE with the GENERIC LOAD

ab92 — Tue, 02 Jan 2018 11:09:30 GMT

Thank you so much Pradosh for your help, your script is working I have this result :

But it is possible to create a specific table like in this example it definitely not useful but for my application yes ..

Sorry for my request, I hope the second is more explanatory, It is possible to join the informations of status.cancelled and status_retunred in one table that we call case1 and the other in one table that we call case2 ?

Many thanks for your reactivity Pradosh,

Axel

Re: Create TABLE with the GENERIC LOAD

pradosh_thakur — Tue, 02 Jan 2018 11:26:21 GMT

to avoid the confusion. you want

1: join status.cancelled and status_retunred to case1

2: others to case 2

Re: Create TABLE with the GENERIC LOAD

ab92 — Tue, 02 Jan 2018 13:23:11 GMT

Only the informations inside the table not necessary to join the OrdersTable...

Please find below an idea about that I want: each case is equal at one table.

Many thanks for your help

Re: Create TABLE with the GENERIC LOAD

pradosh_thakur — Tue, 02 Jan 2018 14:03:26 GMT

I have done something similar to this and its working

Plese check the table names spelling inside wildmatch

noconcatenate

OrdersTable1:

load * resident OrdersTable;

noconcatenate

OrdersTable2:

load * resident OrdersTable;

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

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.Status_Shipped','Flags.Status_Returned') THEN

LEFT JOIN (OrdersTable) LOAD * RESIDENT [$(vTable)];

// DROP TABLE [$(vTable)];

ENDIF

IF WildMatch('$(vTable)', 'Flags.Status_Cancelled','Flags.Status_Returned') THEN

LEFT JOIN (OrdersTable1) LOAD * RESIDENT [$(vTable)];

ENDIF

IF WildMatch('$(vTable)', 'Flags.Status_Shipped','Flags.Status_Active') THEN

LEFT JOIN (OrdersTable2) LOAD * RESIDENT [$(vTable)];

ENDIF

NEXT i

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

LET vTableName1=TableName($(j));

IF WildMatch('$(vTableName1)','Flags.*') then

DROP TABLE [$(vTableName1)];

ENDIF

NEXT j

Re: Create TABLE with the GENERIC LOAD

ab92 — Thu, 11 Jan 2018 14:42:48 GMT

Good afternoon Pradosh,

Many thanks for your help, sorry for the delay...

I tested your script and please find below the view of the table :

I would be possible to delete the synthetic key ?

Many thanks for your help ...

Re: Create TABLE with the GENERIC LOAD

Peter_Cammaert — Thu, 11 Jan 2018 15:55:19 GMT

Use this script code to group tables together. Put it after the GENERIC LOAD. The SUB can appear anywhere, as long as it isn't hidden in another SUB and it is defined before your first CALL:

SUB Group2Tables(vTarget, vSource1, vSource2)

[$(vTarget)]:

NOCONCATENATE

LOAD * RESIDENT [$(vSource1)];

JOIN ([$(vTarget)]) // Perform an OUTER (Natural) JOIN

LOAD * RESIDENT [$(vSource2)];

DROP Tables [$(vSource1)], [$(vSource2)]; // Drop source tables

END SUB

CALL Group2Tables('CASE 1', 'Flags.Status_Cancelled', 'Flags.Status_Returned');

CALL Group2Tables('CASE 2', 'Flags.Status_Active', 'Flags.Status_Shipped');

You may also want to use better names for the target tables.

Best,

Peter

Re: Create TABLE with the GENERIC LOAD

ab92 — Thu, 11 Jan 2018 17:09:45 GMT

Hi Peter,

Really I don't know how can I say.. but thank you so much for your help Peter, it's amazing job and very effective !

I'm working on this problem since many time...

Best regards,

Axel

Re: Create TABLE with the GENERIC LOAD

Peter_Cammaert — Fri, 12 Jan 2018 12:22:07 GMT

Glad I could help.

Enjoy QlikView and the Qlik Community !

Best,

Peter