topic Re: Table Not populating in QlikView

Table Not populating

anonymous1 — Sat, 16 Nov 2024 02:55:04 GMT

hi,

i have the following table:

FIRST_TABLE:
LOAD

PRODUCT_KEY,
COUNTRY,

STORE,
Date(MONTH, 'DD-MMM-YYYY') as MONTH,
num(AMOUNT) as AMOUNT,
'' as FLAG

FROM
[C:\sales_amount.qvd]
(qvd);

I need to create a new table that has all the PRODUCT_KEYS ending in 'A' from FIRST_TABLE and the FLAG column will now be populated with '1'.

Temp_AllCountries:
LOAD
Chr(39) & Concat(DISTINCT COUNTRY, Chr(39) & ',' & Chr(39)) & Chr(39) as ALL_COUNTRIES
resident FIRST_TABLE;

Let vAllCountries = FieldValue('ALL_COUNTRIES', 1);

DROP TABLE Temp_AllCountries;

for each vCountry in '$(vAllCountries)'

NEW_TABLE:
LOAD

PRODUCT_KEY as Product_Key,
COUNTRY as Country,

STORE as Store,
Date(MONTH, 'DD-MMM-YYYY') as Month,
num(AMOUNT) as Amount,
1 as Flag

resident FIRST_TABLE
where right(PRODUCT_KEY, 1) = 'A'
and COUNTRY = '$(vCountry)';

....

so my problem is that NEW_TABLE isn't populating. any ideas why?

Re: Table Not populating

Miguel_Angel_Baeyens — Wed, 24 Jul 2019 14:18:34 GMT

Which errors do you see when debugging the script? Do the variables populate correctly and loop through every possible value?

What if you do the load manually to make sure that filters in WHERE apply correctly?

That code looks very familiar, did you check for missing quotes, commas, etc.?

Re: Table Not populating

anonymous1 — Wed, 24 Jul 2019 14:21:58 GMT

there are no errors, and the variable populates correctly. i can also confirm the WHERE clause works aswell. i assumed the loop was the problem? im very new to QlikView so i cant see what it is Ive done wrong

Re: Table Not populating

pepecc — Wed, 24 Jul 2019 14:22:22 GMT

Try cleaning spaces at the end of product_key in your where clause. Something like this:

resident FIRST_TABLE
where right(rtrim(PRODUCT_KEY), 1) = 'A'
and COUNTRY = '$(vCountry)';

Hope it helps.

Re: Table Not populating

Brett_Bleess — Mon, 19 Aug 2019 15:42:56 GMT

Only extra thing I have is the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/Loops-in-the-Script/ba-p/1473543

There are other posts in this area, so feel free to search yourself, just back the URL up to the Qlik-Design-Blog/ level and you can search from there. If the poster above me was correct, please be sure to give them credit by using Accept as Solution button to mark the post...

If you figured out something else, you can post that and mark it as the solution as well.

Regards,
Brett