topic Re: Table missing after creating them in do while loop in App Development

Table missing after creating them in do while loop

LiquidSword — Tue, 02 Jul 2024 09:09:31 GMT

The table CrossSales_Status_Result is missing after creating it in do while loop. I want to create a table referencing it in [Customer_List_Existing] after exiting the loop.

Set a=0;

LET vCount = NoOfRows('Customer_List');

Do while a< $(vCount);

(....)

NoConcatenate
CS_Temp10:
Load *,
Customer_Code & '_' & MonthYear as CrossSales_Key,
Customer_Code & '_' & MonthYear as Customer_Key;
Load Customer_Code,
MonthYear,
CrossSales_Status,
Date(Date#(CrossSales_Date, 'YYYYMMDD'), 'D/M/YYYY') as CrossSales_Date;
Resident CS_Temp9;

Drop Table CS_Temp9;

CrossSales_Status_Result:
Load *,
Cross_Cate & '_' & CrossSales_Key as CrossSales_Key_v2;
Load Distinct Customer_Code,
CrossSales_Date,
CrossSales_Key,
Replace(Replace(Replace(Replace(CrossSales_Status,'+Promotion',''), '+promotion', ''), '->Promotion', ''), '->promotion','') as CrossSales_Status,
SubField(CrossSales_Status , '->', 2) as Cross_Cate
Resident CS_Temp10
where NOT ISNULL(CrossSales_Status);

Drop Table CS_Temp10;

CS_Temp11:
Load Distinct CrossSales_Date
Resident CrossSales_Status_Result;

Join (CS_Temp11)

CS_Temp12:
Load Distinct CrossSales_Status
Resident CrossSales_Status_Result;

Concatenate (CrossSales_Status_Result)
ALSKDLS:
Load CrossSales_Date,
CrossSales_Status,
1 & '_' & date(MonthEnd(CrossSales_Date), 'MMM-YYYY') as CrossSales_Key,
1 as Customer_Code
Resident CS_Temp11;

(...)

Let a=a+1;

Loop

Customer_List_Existing:
Load Customer_Code as Cust_ID
Resident CrossSales_Status_Result;

Re: Table missing after creating them in do while loop

Vegar — Tue, 02 Jul 2024 09:34:36 GMT

Are you sure that it is missing? or is it just not containing the data that you expect?

You have some (...) are they manipulating the `CrossSales_Status_Result` table in any way. If so it could be that you don't get your expected autoconcatenate of `CrossSales_Status_Result` to work as expected in the beginning of your loop.

Steps to troubleshoot.

- Try to add an exit script after the loop to see what's in your datamodel

- Try to add an exit script at the end of the loop. The script stops at the end of first iteration. Check what tables and fields that are in your datamodel.

Re: Table missing after creating them in do while loop

LiquidSword — Tue, 02 Jul 2024 09:44:59 GMT

hi, (...) is used to replace paragraphs of script.

Anyways, I added the following inline table in the loop (random table just for testing) and exit script after the loop, and the table is missing.

Table:

LOAD * Inline [

ID , brand , criteria , assesement

123 , abc , Q9, Y

145 , abc , Q9, Y

123 , abc , P11, Y

145 , abc , P11, N

];

I then tried adding exit script between "Let a=a+1;" and "Loop" and also after "Loop". [Table] is still missing from data model.

Re: Table missing after creating them in do while loop

LiquidSword — Tue, 02 Jul 2024 09:57:56 GMT

This is the entire segment:

NoConcatenate

Customer_List_Temp:

Load Distinct Customer_Code, Item_Code

Resident Item_Sales

Where Item_Amount <> '0'

and ( NUM(Transaction_Date) >= Num(MonthStart(AddMonths(Today(), -3))) and NUM(Transaction_Date) <= Num(MonthEnd(AddMonths(Today(), -1))) )

//and ( NUM(Transaction_Date) >= Num(MonthStart(AddMonths(Today(), -1))) and NUM(Transaction_Date) <= Num(MonthEnd(AddMonths(Today(), -1))) )

;

left join (Customer_List_Temp)

Load Item_Code, Master_Category

Resident Item_Master;

NoConcatenate

Customer_List:

Load Distinct Customer_Code

Resident Customer_List_Temp

where Item_Code <> 'CD' and Not ISNULL(Master_Category)

//and Customer_Code like 'LMC21*';

;

Drop Table Customer_List_Temp;

Set a=0;

LET vCount = NoOfRows('Customer_List');

Do while a< $(vCount)

Let vCustomer = peek('Customer_Code',$(a) ,'Customer_List');

CS_Temp0:

Load Customer_Code,

Transaction_Date,

Item_Code

Resident Item_Sales

//where Customer_Code = 'LMC14-0102'

where Customer_Code = '$(vCustomer)'

//or Customer_Code = 'LMC14-0075' or Customer_Code = 'LMC14-0102' or Customer_Code = 'LMC14-0146' or Customer_Code = 'LMC14-0178'

//where Item_Amount <> '0'

and Item_Amount <> '0'

;

Left Join (CS_Temp0)

Load Item_Code,

Master_Category

Resident Item_Master;

CS_Temp0_1:

Load Customer_Code,

Date(Num(MonthEnd(Transaction_Date)), 'MMM-YYYY') as MonthYear,

Transaction_Date,

Master_Category

Resident CS_Temp0

where Not Master_Category LIKE '*+*';

NoConcatenate

//Qualify*;

CS_Temp0_2:

Load *

Resident CS_Temp0_1

Order By Customer_Code, MonthYear asc;

//Unqualify*;

Drop Table CS_Temp0, CS_Temp0_1;

NoConcatenate

CS_Temp0_3:

Load Customer_Code,

MonthYear as MonthYear,

Transaction_Date,

Master_Category as Category,

if(Peek(Customer_Code) = Customer_Code, Peek(NodeID)+1, 1) as NodeID

Resident CS_Temp0_2

where NOT ISNULL(Master_Category)

Order by Customer_Code, Transaction_Date asc;

NoConcatenate

CS_Temp0_4:

Load Customer_Code,

// Customer_Code as Customer,

MonthYear,

Transaction_Date,

Category,

NodeID,

if(Peek(Customer_Code) = Customer_Code, Peek(NodeID), '') as ParentID

// if(Peek(Customer_Code) = Customer_Code, '1', '') as ParentID

Resident CS_Temp0_3

Order by Customer_Code, Transaction_Date asc;

NoConcatenate

CS_Temp0_5:

Load Customer_Code as Customer,

MonthYear,

Transaction_Date,

Category,

NodeID,

ParentID

Resident CS_Temp0_4

Order by Customer_Code, Transaction_Date asc;

Drop Table CS_Temp0_2, CS_Temp0_3

, CS_Temp0_4

;

//exit script;

// NoConcatenate

// CS_Temp1:

// Load * inline [

// Customer, MonthYear, NodeID, ParentID, Category, Transaction_Date

// A, 01-2020, 1, , HR, 12/01/2020

// A, 02-2020, 2, 1, FA, 11/02/2020

// A, 03-2020, 3, 2, HR, 13/03/2020

// A, 04-2020, 4, 3, Body, 14/04/2020

// A, 05-2020, 5, 4, FA, 15/05/2020

// B, 01-2020, 1, , FA, 12/01/2020

// B, 01-2020, 2, 1, HR, 13/01/2020

// B, 03-2020, 3, 2, HR, 13/03/2020

// B, 04-2020, 4, 3, FA, 14/04/2020

// B, 05-2020, 5, 4, Body, 15/05/2020

// ];

NoConcatenate

CS_Temp1_1:

Load Customer,

MonthYear,

Transaction_Date,

IF(Peek(MonthYear) = MonthYear AND PEEK(Customer) = Customer, '1', '0') as Flag

//Resident CS_Temp1;

//Resident CS_Temp0_4;

Resident CS_Temp0_5;

NoConcatenate

CS_Temp1_2:

Load Customer & MonthYear as JoinKey,

Sum(Flag) as Flag

Resident CS_Temp1_1

Group By Customer, MonthYear;

Drop Table CS_Temp1_1;

NoConcatenate

CS_Temp2:

Hierarchy (NodeID, ParentID, Category)

Load

Customer,

MonthYear,

Transaction_Date,

NodeID,

ParentID,

Re: Table missing after creating them in do while loop

Vegar — Tue, 02 Jul 2024 11:50:36 GMT

You write that not even your inline gets generated.

Are you sure that you ever get into the loop? Do you have rows in your Customer_List table?

Try printing out some validation figures before running the loop by adding the bold section in script snipplet below. Just to check that

Set a=0;

LET vCount = NoOfRows('Customer_List');

trace a=$(a);

trace vCount=$(vCount);

LET loop_condition = a< $(vCount) ; //The same as your do while loop is using
trace loop_condition=$(loop_condition);

Do while a< $(vCount)

...

loop

Re: Table missing after creating them in do while loop

LiquidSword — Tue, 02 Jul 2024 14:54:42 GMT

yes, Customer_List is indeed empty! i was stuck on this for so long thinking the answer was from inside the loop. you're brilliant!

Re: Table missing after creating them in do while loop

Vegar — Tue, 02 Jul 2024 15:02:15 GMT

Thanks for the feedback. 👍 Glad I could help. 😊